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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Arthur M(dot) Kang" <arthur(at)levelogic(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: SELECT DISTINCT ON... ORDER BY...
Date: 2000-10-27 00:55:28
Message-ID: 24596.972608128@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Makiko Kudo 2000-10-27 02:05:28 temporary
Previous Message David C Mudie 2000-10-27 00:45:49 Re: PostgreSQL General Digest V1 #764