Re: SELECT DISTINCT ON... ORDER BY...

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Arthur M. Kang" <arthur(at)levelogic(dot)com> writes:
> Is there a way to select distinct on one column and sort by another?

No: the DISTINCT ON column(s) must be the initial sort keys, although
you are allowed to specify more keys than just the distinct columns.
(If you do that, you can control which tuple gets selected as the
representative of each DISTINCT group.)  There is no good alternative
since DISTINCT is just a "unique" filter and must have its input sorted
by the columns you want to DISTINCT on.

In 7.1 it will be possible to do what you want using a subquery:

SELECT * FROM (SELECT DISTINCT ...) subselect
ORDER BY whatever;

which will produce a plan with two levels of sorting (something that
7.0 will never do).

For now, a workaround is to do the SELECT DISTINCT into a temp table
and then do a SELECT ... ORDER BY from the temp table.

			regards, tom lane