Απορία SQL

[V13]

On Monday 07 of February 2011, V13 wrote:
> On Monday 07 of February 2011, Antonis Christofides wrote:
> > CREATE TABLE wiki_page(
> > id INTEGER NOT NULL PRIMARY KEY,
> > "name" VARCHAR(100) NOT NULL
> > );
> > 
> > CREATE TABLE wiki_pageversion(
> > page INTEGER NOT NULL REFERENCES wiki_page(id),
> > version_number INTEGER NOT NULL,
> > content TEXT NOT NULL,
> > CONSTRAINT pk PRIMARY KEY (page, version_number)
> > );
> 
> An den skeytomai kati lathos, to parakato kanei ayto poy theleis:
> 
> SELECT a.id, MAX(b.version_number) FROM wiki_page a
>   LEFT JOIN wiki_pageversion b ON a.id=b.page
>   GROUP BY a.id;
> 
> kai isos na einai pio grigoro.

Tora poy ksanablepo to olo thread den mporo na katalabo giati den soy kanei to 
aplo:

SELECT page, MAX(version_number) FROM wiki_pageversion
  GROUP BY page;

H an theleis kai to periexomeno:

SELECT a.page, a.version_number, b.content FROM (
  SELECT page, MAX(version_number) FROM wiki_pageversion
    GROUP BY page
  ) a LEFT JOIN wiki_pageversion b USING(page, version_number);

Me alla logia o pinakas wiki_page den fainetai na xreiazetai sto sygkekrimeno 
erotima.