db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tom_ <tom12...@web.de>
Subject Re: distinct and order by
Date Sat, 02 Feb 2008 12:52:15 GMT

If a rearrangement of the SQL can do this, wouldn't the right place be in
Derby's SQL parser/optimizer?  


Knut Anders Hatlen wrote:
> 
> tom_ <tom12167@web.de> writes:
> 
>> The errormessage is
>>
>> The ORDER BY clause may not specify an expression, since the query
>> specifies
>> DISTINCT
>> [Error Code: 20000]
>> [SQL State: 4287A]
>>
>> The statement is
>>
>> select distinct 
>> t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3 
>> from 
>> t1, t2, t3  
>> where 
>> ...
>> order by lower(t2.t2value2) , lower(t2.t2value1) , lower(t2.t2value3) 
> 
> As a workaround, you may try to do SELECT DISTINCT in a subquery and do
> the ORDER BY in the outer query:
> 
> select * from
>   (select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3
>      from t1, t2, t3 where ....) as sub(id, val1, val2, val3)
> order by lower(val1), lower(val2), lower(val3)
> 
> -- 
> Knut Anders
> 
> 

-- 
View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15242535.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message