"Thalis A. Kalfigopoulos" <thalis@cs.pitt.edu> writes:
> It's a simple select/group by:
> select nid,type,max(version) from node group by nid,type;
That solves the problem as stated, but most likely there are more
columns in the table and what's really wanted is the whole row
containing the max version number. The above doesn't work in that
case.
AFAIK the only way to solve the extended problem in standard SQL is
select * from node outside
where version = (select max(version) from node inside
where outside.nid = inside.nid and outside.type = inside.type);
The fact that this is standard is the only thing going for it :-(.
It's ugly, it's likely to be horribly slow, and it gets much worse
if you might have ties in the "version" column that you need to break
somehow (eg, by then choosing the latest timestamp among the rows
with maximal version).
If you don't mind nonstandard SQL, then this is the kind of problem
that DISTINCT ON was invented for:
select distinct on (nid,type) * from node
order by nid, type, version desc;
which gets the whole job done with one sort-and-uniq pass. See the
weather-report example in the SELECT reference page.
regards, tom lane
