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 Thu, 31 Jan 2008 21:10:37 GMT

The problem is that I have to make a release that includes this sql select.


Stanley Bradbury wrote:
> 
> tom_ wrote:
>> 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) 
>>
>>
>>
>>
>> Dyre.Tjeldvoll wrote:
>>   
>>> tom_ <tom12167@web.de> writes:
>>>
>>>     
>>>> I am using "disctinct" because of some self-joins and also needed to
>>>> add
>>>> an
>>>> "order by" clause. An error is shown. Is it not possible to use
>>>> distinct
>>>> and
>>>> order by together?
>>>>       
>>> I think it is allowed. Executing
>>>
>>> select distinct * from sys.systables order by tablename;
>>>
>>> in ij works just fine. Could you show the error message you get, and
>>> perhaps what the table looks like?
>>>
>>> -- 
>>> dt
>>>
>>>
>>>     
> 
> Hi Tom -
> 
> I see what you mean using the demo DB toursDB:
> 
> ij> select * from airlines order by lower(airline_full);
> A&|AIRLINE_FULL            |BASIC_RATE            |DISTANCE_DISCOUNT     
> |BUSINESS_LEVEL_FACTOR 
> |FIRSTCLASS_LEVEL_FACT&|ECONOMY_SE&|BUSINESS_S&|FIRSTCLASS&
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
> AA|Amazonian Airways       |0.18                  |0.03                  
> |0.5                   |1.5                   |20         |10         |5
> US|Union Standard Airlines |0.19                  |0.05                  
> |0.4                   |1.6                   |20         |10         |5
> 
> 2 rows selected
> ij> select distinct * from airlines order by lower(airline_full);
> ERROR 4287A: The ORDER BY clause may not specify an expression, since 
> the query specifies DISTINCT.
> ij> select distinct airline_full from airlines order by
> lower(airline_full);
> ERROR 4287A: The ORDER BY clause may not specify an expression, since 
> the query specifies DISTINCT.
> ij>
> 
> I didn't find a JIRA enhancement to remove this restriction.  I suggest 
> you file an Enhancement request to remove the restriction reported by 
> ERROR 4287A.
> 
> 
> 
> 
> 

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


Mime
View raw message