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

Re: select distinct w/order by

From: Richard Huxton <dev(at)archonet(dot)com>
To: "John Liu" <johnl(at)emrx(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: select distinct w/order by
Date: 2004-03-31 20:37:19
Message-ID: 200403312137.19552.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
On Wednesday 31 March 2004 18:50, 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;

Can you explain what this means? If I have

atcode | torder
AAA    | 20
BBB    |  5
CCC    | 10
BBB    | 45
CCC    | 27

What order should we get?
You could argue for:
1. BBB,CCC,AAA since that is the order of the min(torder)
2. AAA,CCC,BBB since that is the order of the max(torder)
3. AAA,BBB,CCC if you take the first(torder) you find reading down the page
4. AAA,CCC,BBB if you take the first(torder) but read up the page

Which one should PG pick, and how should it know?

Which one did the other database pick, and why was it right to do so?

-- 
  Richard Huxton
  Archonet Ltd

In response to

Responses

pgsql-general by date

Next:From: Bob.HenkelDate: 2004-03-31 20:52:00
Subject: Re: select distinct w/order by
Previous:From: William WhiteDate: 2004-03-31 20:32:48
Subject: Re: Question about rtrees (overleft replacing left in nodes)

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