Re: select distinct w/order by

I don't think PG allows your query -
select distinct atcode from tmp group by atcode order by max(torder);
ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list

I can't make Tom's query work either -
select atcode from (select distinct on (atcode) atcode, torder from tmp
order by atcode, max(torder)) ss order by torder;
ERROR:  Attribute tmp.atcode must be GROUPed or used in an aggregate
function

Thanks for providing the alternative trials.

johnl


-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, March 31, 2004 4:35 PM
To: Tom Lane
Cc: John Liu; pgsql-general@postgresql.org
Subject: Re: [GENERAL] select distinct w/order by

On Wed, 31 Mar 2004, Tom Lane wrote:

> "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.

I did it like this:

select atcode from table group by atcode order by max(toorder);

Is that equivalent?