From: | "J(dot)R(dot) Onyschak" <jonyschak(at)nvisia(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: PostgreSQL bug in SELECT DISTINCT |
Date: | 2001-05-03 18:07:44 |
Message-ID: | 3AF19E70.6050404@nvisia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Tom Lane wrote:
>"J.R. Onyschak" <jonyschak(at)nvisia(dot)com> 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?
From | Date | Subject | |
---|---|---|---|
Next Message | J.R. Onyschak | 2001-05-03 18:13:50 | Re: PostgreSQL bug in SELECT DISTINCT |
Previous Message | Bruce Momjian | 2001-05-03 17:42:39 | Re: Documentation bug for pg_ctl (-s option not documented) |