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.