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

Not that this is the issue, but what kind of tool where you using to get
your results back with this "other" database?  Sometimes these fancy GUI
tools like to be smart on you and order things based on something it feels
is correct giving you the impression that the database choose the order
when infact the GUI tool choose the order.  Just a thought


Bob Henkel          651-738-5085
Mutual Funds I/T Woodbury
Hartford Life
500 Bielenberg Drive
Woodbury, MN 55125


|---------+---------------------------------->
|         |           Richard Huxton         |
|         |           <dev@archonet.com>     |
|         |           Sent by:               |
|         |           pgsql-general-owner@pos|
|         |           tgresql.org            |
|         |                                  |
|         |                                  |
|         |           03/31/2004 02:37 PM    |
|         |                                  |
|---------+---------------------------------->

>------------------------------------------------------------------------------------------------------------------------------|
  |
        | 
  |       To:       "John Liu" <johnl@emrx.com>, <pgsql-general@postgresql.org>
        | 
  |       cc:
        | 
  |       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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)







*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information.  If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited.  If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies. 
*************************************************************************