From: | "J(dot)R(dot) Onyschak" <jonyschak(at)nvisia(dot)com> |
---|---|
To: | lockhart(at)fourpalms(dot)org |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: PostgreSQL bug in SELECT DISTINCT |
Date: | 2001-05-03 18:13:50 |
Message-ID: | 3AF19FDE.8080309@nvisia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Thomas Lockhart wrote:
>>When I execute the following query:
>>SELECT DISTINCT title FROM division ORDER BY UPPER(title);
>>I get:
>>ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list
>>If I remove DISTINCT, the query works fine.
>>Is this illegal or a known bug?
>>
>
>Illegal. I believe that allowing any function call in the "order by"
>clause is an extension to SQL9x, so feel lucky that you can do it at all
>;)
>
Where can I look at a copy of the SQL spec? I've tried to locate it
online, but haven't been able to find it.
>
>
>However,
>
>SELECT T FROM (SELECT DISTINCT title FROM division) AS T ORDER BY
>UPPER(T);
>
>seems to work in 7.1 (but not in earlier releases). This give PostgreSQL
>a chance to hold an intermediate result to sort in a second pass.
>
> - Thomas
>
That's interesting. I remember that, but I don't think I should rely on
it because I am trying to keep our SQL code portable across databases.
(We've already made once change and might have to do another)
Thanks for your time,
J.R.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-05-03 18:14:49 | Re: \dT macaddr helptext bug. |
Previous Message | J.R. Onyschak | 2001-05-03 18:07:44 | Re: PostgreSQL bug in SELECT DISTINCT |