db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Vatter (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3373) SQL "distinct" and "order by" needed together
Date Fri, 01 Feb 2008 11:05:08 GMT
SQL "distinct" and "order by" needed together
---------------------------------------------

                 Key: DERBY-3373
                 URL: https://issues.apache.org/jira/browse/DERBY-3373
             Project: Derby
          Issue Type: Improvement
          Components: SQL
    Affects Versions: 10.3.2.1
         Environment: Solaris Dev Express, Java 5
            Reporter: Thomas Vatter
            Priority: Blocker
             Fix For: 10.3.2.2


I am pasting here the communication from the mailinglist. I am having a blocking and large
problem with it because I have to make a release that needs the specified SQL query. 


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@...> 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 
>> 
>> 
>>    
«  [hide part of quote]

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. 



-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message