Thread

Re: PostgreSQL bug in SELECT DISTINCT

Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-05-03T15:19:32Z

> 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