[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] select distinct w/order by
From:       "scott.marlowe" <scott.marlowe () ihs ! com>
Date:       2004-03-31 22:35:26
Message-ID: Pine.LNX.4.33.0403311534410.26942-100000 () css120 ! ihs ! com
[Download RAW message or body]

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?


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html
[prev in list] [next in list] [prev in thread] [next in thread]