db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Nielsen <Thomas.Niel...@Sun.COM>
Subject Re: Problem with "order by"
Date Fri, 18 Apr 2008 21:04:29 GMT
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
>
>


Mime
View raw message