Thank you for this possibility, though it is difficult for me to apply this change because the sql query is one of a lot that are generated by an sql query generator. I would have to hange this very complicated code and make it even more complicated - and later on rechange the code back.
Knut Anders Hatlen wrote: > > tom_ <[EMAIL PROTECTED]> writes: > >> 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) > > As a workaround, you may try to do SELECT DISTINCT in a subquery and do > the ORDER BY in the outer query: > > select * from > (select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3 > from t1, t2, t3 where ....) as sub(id, val1, val2, val3) > order by lower(val1), lower(val2), lower(val3) > > -- > Knut Anders > > -- View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15227107.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
