Hi Bradley,
Your problem is really not that bizarre. Using a order by clause in a
subquery is actually not allowed in the SQL standard (neither SQL-99 nor
SQL-2003) if you read the spec closely.
I can only speculate why other DBs have decided to deviate from the
standard. Derby attempts to adhere to it.
HTH,
Thomas
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
