Re: select distinct w/order by - Mailing list pgsql-general

From John Liu
Subject Re: select distinct w/order by
Date
Msg-id 200403312113.i2VLDHcU027061@mail.stihealthcare.com
Whole thread Raw
In response to Re: select distinct w/order by  (Richard Huxton <dev@archonet.com>)
List pgsql-general
Tom provided the same logic arguments. I also like the way 'simple is
better' as long as it sticks with SQL requirement. But in practice, you have
to face such issue even it's 'catch 22' which depends on the application
design -

For the your case -
code                  codeid

AAA                       20
BBB                        5
CCC                       10
BBB                       45
CCC                       27

When issue "select distinct code from test1 order by codeid;"
One of the database returns using their internal rule (at least it's
constant itself) -
code

BBB
CCC
AAA

It provides one of the arguable result sets.

But think about another situation, the result is for sure -
code                  code2                 codeid

a1                    a                          1
a2                    a                          2
b1                    d                          3
b2                    d                          4
c1                    c                          5
c2                    c                          6

select distinct code2 from test2 order by codeid;
code2

a
d
c

It's handy.

I hope everything is black or white, but it's not. The user has the choice
at least. But when I use PostgreSQL, I need find an alternative solution to
handle such issue.

johnl


-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, March 31, 2004 2:37 PM
To: John Liu; pgsql-general@postgresql.org
Subject: Re: [GENERAL] select distinct w/order by

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


pgsql-general by date:

Previous
From: "John Liu"
Date:
Subject: Re: select distinct w/order by
Next
From: Bernhard Ankenbrand
Date:
Subject: Crash in postgres/linux on verly large database