Skip site navigation (1) Skip section navigation (2)

Re: select distinct w/order by

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: John Liu <johnl(at)emrx(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: select distinct w/order by
Date: 2004-03-31 21:35:10
Message-ID: 20040331133127.A80037@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2004-03-31 21:36:12
Subject: Re: Question about rtrees (overleft replacing left in nodes)
Previous:From: William WhiteDate: 2004-03-31 21:21:19
Subject: Re: Question about rtrees (overleft replacing left in nodes)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group