Re: PostgreSQL bug in SELECT DISTINCT - Mailing list pgsql-bugs

> 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
;)

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