Re: PostgreSQL bug in SELECT DISTINCT

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.