db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: distinct and order by
Date Fri, 01 Feb 2008 12:25:25 GMT
tom_ <tom12167@web.de> 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

Mime
View raw message