Re: problem query ...

John Taylor <postgres(at)jtresponse(dot)co(dot)uk> writes:
> On Thursday 31 January 2002 17:35, Frank Bax wrote:
>> select mytable.* from mytable, (select id,max(update) as update from
>> mytable group by id) maxes where mytable.id = maxes.id and mytable.update =
>> maxes.update;

> I didn't think of putting the subselect there, I was trying in the
> where clause. However, I think using distinct on is probably going to
> run faster.

DISTINCT ON will certainly be quicker. Another point is that the
behavior isn't necessarily exactly the same. Suppose that id/update
isn't unique. Frank's query will give you all the rows with the
maximal update value for each id value. DISTINCT ON will give you
only one of those rows --- one chosen at random, if you just order
by id and update, or you can order by additional columns to determine
which of the possible rows is selected. So depending on the behavior
you actually want, either way might be more appropriate.

BTW, although I faulted DISTINCT ON for being nonstandard, subselect
in the FROM clause isn't necessarily portable either; it is standard
but a lot of allegedly-SQL DBMSes don't support it (including Postgres
prior to 7.1). If you wanted to do this in a way that's actually
portable, you might have to create a temp table, do the select max/group
by id into the temp table, and then join the temp table against the
original. Yech.

regards, tom lane