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_ <[EMAIL PROTECTED]> 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.
