db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Problem with "order by"
Date Fri, 18 Apr 2008 21:18:25 GMT
Hi Brad,

As Thomas points out, Derby hews closely to the SQL standard, which does 
not allow an ORDER BY clause in subqueries. The gnarly details of the 
standard grammar can be found here: 
http://savage.net.au/SQL/sql-2003-2.bnf.html

Regards,
-Rick

Bradley Munz wrote:
> Hi there,
>
> I have a bizarre problem with "order by" usage in Derby sql. Below is 
> a simple table from that contains some availability data:
>
> /CREATE TABLE agent_availabilty_rt/
> /agent_id integer,/
> /username varchar(30) UNIQUE, /
> /reserved tintyint DEFAULT 1,/
> /availtime bigint,/
> /PRIMARY KEY(agent_id))/
>
> I need to extract this data and "join" it with another table (which 
> isn't of importance in this email):
>
> /select agent_avail.username,agent_avail.availtime /
> /from (/
> /    select username,agent_id,availtime /
> /    from agent_availabilty_rt /
> /    where agent_availabilty_rt.reserved = 0 /
> /    order by agent_availabilty_rt.availtime asc/
> /) agent_avail/
>
> For some reason derby doesn't like this bit of sql (However it does 
> work on other DBs like postgres). If I remove the "order by" I.e:
>
> /select agent_avail.username,agent_avail.availtime /
> /from (/
> /    select username,agent_id,availtime /
> /    from agent_availabilty_rt /
> /    where agent_availabilty_rt.reserved = 0 /
> /    //order by agent_availabilty_rt.availtime asc/
> /) agent_avail /
>
> it works fine.
>
> Is there something I'm missing here?
>
> Thanks in advance.
> Brad Munz
>
>


Mime
View raw message