Thread: select distinct on

Hello,

I am trying to use SELECT DISTINCT ON.  This works as intended:
    SELECT DISTINCT ON (f1) * from table ORDER BY f1

 f1 | f2 | f3
----+----+----
  1 |  2 |  3
  2 |  2 |  2
  3 |  1 |  2

However, I am trying to order the results by non-distinct fields f2, f3
to get:

 f1 | f2 | f3
----+----+----
  3 |  1 |  2
  2 |  2 |  2
  1 |  2 |  3

Clearly this does not work:
    SELECT DISTINCT ON (f1) * from table ORDER BY f2, f3, f1

So is there some other way without SELECT INTO a temporary table?
Thanks in advance.
Dave
--
Dave Ahn | ahn@vec.wfubmc.edu | Wake Forest University Baptist Medical Center

When you were born, you cried and the world rejoiced.  Try to live your life
so that when you die, you will rejoice and the world will cry.  -1/2 jj^2

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