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
