Re: distinct and order by

[tom_ <to...@web.de>]

I have created a new issue.


tom_ wrote:
> 
> 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_ <to...@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-tp15206572p15224665.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.