db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dyre Tjeldvoll (JIRA)" <j...@apache.org>
Subject [jira] Assigned: (DERBY-3373) SQL "distinct" and "order by" needed together
Date Wed, 19 Mar 2008 15:48:24 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dyre Tjeldvoll reassigned DERBY-3373:
-------------------------------------

    Assignee: Bryan Pendleton  (was: Dyre Tjeldvoll)

Sorry. I changed the wrong issue. Changing the assignee back to Bryan. 

> SQL "distinct" and "order by" needed together
> ---------------------------------------------
>
>                 Key: DERBY-3373
>                 URL: https://issues.apache.org/jira/browse/DERBY-3373
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.2.1
>         Environment: Solaris Dev Express, Java 5
>            Reporter: Thomas Vatter
>            Assignee: Bryan Pendleton
>            Priority: Blocker
>             Fix For: 10.3.2.2, 10.4.0.0
>
>         Attachments: allowExpressions.diff, mergeWith2351.diff
>
>
> 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