Re: select distinct w/order by

"John Liu" <johnl@emrx.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