Re:

On Mon, 08 Oct 2001 10:33:01 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "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.

The problem is you're both right.

> 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);

Unfortunately I need to retain backward compatibility with MySQL
which doesn't support sub-selects.

At this (early) point I think I can refactor the tables
appropriately, use the simple GROUP BY Thalis suggested, and then
use that for an inner join to get what I really want.  I don't know
what the performance curves of temp tables are like, but at least in
quick testing under psql it works...

> which gets the whole job done with one sort-and-uniq pass.  See
> the weather-report example in the SELECT reference page.

Cute.  Thanks, I had not noticed that.

--
J C Lawrence
---------(*)                Satan, oscillate my metallic sonatas.
claw@kanga.nu               He lived as a devil, eh?
http://www.kanga.nu/~claw/  Evil is a name of a foeman, as I live.