Re: select distinct on

Dave Ahn <ahn@vec.wfubmc.edu> writes:
> Clearly this does not work:
>     SELECT DISTINCT ON (f1) * from table ORDER BY f2, f3, f1

No; the implementation of DISTINCT requires the DISTINCT fields to be
the major sort keys, so you have to ORDER BY f1,f2,f3 instead.

If you want the final output sorted differently, in 7.0 you don't really
have much choice but to do the SELECT DISTINCT into a temporary table.
In 7.1 it'll be possible to do it via subselect-in-FROM:

    SELECT * FROM
      (SELECT DISTINCT ON (f1) * FROM table ORDER BY f1, f2, f3) tmp
    ORDER BY f2, f3, f1;

but in 7.0 there's no way to cause the system to do multiple sorting
passes in one query.

            regards, tom lane