Re: select distinct w/order by

On Wed, 31 Mar 2004, John Liu wrote:

> I know this is an old topic, but it's not easy to find a way around it, so
> when we migrate SQL from other database to PostgreSQL, it causes a huge
> headache. Here's an extremely simple example -
>
> The original simple SQL -
> select distinct atcode from TMP order by torder;
>
> (it'll error out in PostgreSQL, although SQL92 extension may allow it;
> there's time you just can't do "select distinct atcode,torder from TMP order
> by torder"!!)
>
> I tried to rewrite the above simple query in PostgreSQL as - select distinct
> atcode from (select atcode,torder from TMP order by torder) t;
>
> Can anybody provide a real/general solution to the above practical problem?
> (Tom?) This causes postgreSQL users too much time and headache.

Is atcode unique or can you assume that the torder values are the same for
different rows of the same atcode?

In general, I think something of the general form:
select atcode from TMP group by atcode order by min(torder);
may actually give results resembling what you want.