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

Reply via email to