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
