[BUGS] PostgreSQL bug in SELECT DISTINCT
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? thanks for any help, J.R. PostgreSQL 7.1 Mandrake 8.0 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] PostgreSQL bug in SELECT DISTINCT
Tom Lane wrote: >"J.R. Onyschak" <[EMAIL PROTECTED]> writes: > >>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? >> > >This is not a bug, but an intentional restriction to prevent ill-defined >query results. Why don't you just "ORDER BY title"? > > regards, tom lane > I can't/don't want to "ORDER BY title" because the title might be entered as upper case or lower case. If we had divisions with titles Transportation, parks, and Education. I would like to display the results alphabetical regardless of capitalization. I know this example is a little contrived because all divisions should be capitalized, but we have a number of "objects" backed by tables that have a title column that we order by and some of them have a high chance of having mixed capitalization.I can understand the prevention of ill-defined query results, but is PostgreSql being too restrictive? I am ordering by a column in the select clause, I am just using a function on that column. Thanks for the great product. It truely has been fun using PostgreSql.Very robust, very easy to use. Thank you for your help, jr P.S. I don't mean for this to sound whiny, but I encounterd this in porting a project from using Oracle to PostgreSql, so I know that Oracle supports it and other people might run into this problem. P.P.S. Where can I locate a copy of the latest SQL spec? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
