Re:

On Mon, 08 Oct 2001 10:33:01 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Thalis A. Kalfigopoulos" <thalis(at)cs(dot)pitt(dot)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(at)kanga(dot)nu He lived as a devil, eh?
http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live.