Problem with "order by"

[Bradley Munz <bm...@mhg.co.za>]

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

Re: Problem with "order by"

[Kai Ponte <ka...@perfectreign.com>]

On Friday 18 April 2008 02:18:25 pm Rick Hillegas wrote:
> 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

that makes sense

to do a sub-query anyway would be better served by using something like a temp 
table or simply a join

-- 
kai
www.filesite.org || www.4thedadz.com || www.perfectreign.com
remember - a turn signal is a statement, not a request

Re: Problem with "order by"

[Rick Hillegas <Ri...@Sun.COM>]

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
>
>

Re: Problem with "order by"

[Thomas Nielsen <Th...@Sun.COM>]

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
>
>