From: | "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)hub(dot)org, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ... |
Date: | 1999-01-29 18:24:03 |
Message-ID: | 199901291824.SAA24917@linda.lfix.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
Tom Lane wrote:
>Thomas Metz <tmetz(at)gsf(dot)de> writes:
>> SELECT DISTINCT ON id id, name FROM test ORDER BY name;
>> [doesn't work as expected]
>
>There have been related discussions before on pg-hackers mail list;
>you might care to check the list archives. The conclusion I recall
>is that it's not real clear how the combination of SELECT DISTINCT
>on one column and ORDER BY on another *should* work. Postgres'
>current behavior is clearly wrong IMHO, but there isn't a unique
>definition of right behavior, because it's not clear which tuples
>should get selected for the sort.
>
>This "SELECT DISTINCT ON attribute" option strikes me as even more
>bogus. Where did we get that from --- is it in the SQL92 standard?
I looked through the standard yesterday and couldn't find it. It doesn't
seem to be a useful extension, since it does nothing that you can't do
with GROUP BY and seems much less well defined. For the moment I have
added a brief description to the reference documentation for SELECT.
>If you SELECT DISTINCT on a subset of the attributes to be returned,
>then there's no unique definition of which values get returned in the
>other columns. In Thomas' example:
...
>Any of these are "DISTINCT ON id", but it's purely a matter of
>happenstance table order and unspecified implementation choices which
>one will appear. Do we really have (or want) a statement with
>inherently undefined behavior?
We have it; I suggest we don't want it!
--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"My son, if sinners entice thee, consent thou not."
Proverbs 1:10
From | Date | Subject | |
---|---|---|---|
Next Message | Clark Evans | 1999-01-29 20:16:02 | License for PostgreSQL Contributions (Was: RE: DOM Implementation for C++) |
Previous Message | Tom Lane | 1999-01-29 18:13:54 | Re: [HACKERS] Postmaster dies with many child processes (spinlock/semget failed) |
From | Date | Subject | |
---|---|---|---|
Next Message | Douglas Nichols | 1999-01-29 19:45:36 | Re: [SQL] Character type name?? How to lower case it? |
Previous Message | David Hartwig | 1999-01-29 17:03:13 | Re: [SQL] ORDER BY |