Re: select distinct w/order by

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "John Liu" <johnl(at)emrx(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: select distinct w/order by
Date: 2004-03-31 19:41:43
Message-ID: 9918.1080762103@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"John Liu" <johnl(at)emrx(dot)com> writes:
> The original simple SQL -
> select distinct atcode from TMP order by torder;

This is not "simple", it is "broken SQL with an undefined result".

If DISTINCT merges multiple rows with the same atcode, how are we
supposed to know which row's value of torder to sort the merged
row on?

Your other database was no doubt making a random choice and giving
you a random result ordering in consequence. You need to think harder
about what behavior you really want.

Once you can define the behavior (ie, just which torder you want to use)
you can probably implement it with something like

select atcode from
(select distinct on (atcode) atcode, torder from table
order by atcode, ???
) ss
order by torder;

where the ??? ordering determines which torder you get in each atcode group.
See the SELECT DISTINCT ON example in the SELECT reference page.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Eckermann 2004-03-31 19:58:15 Re: select distinct w/order by
Previous Message Peter Grossi 2004-03-31 19:33:03 Business packages using postgresql