?

Log in

No account? Create an account
not only but also
geek rant - Oracle, 1981 called, they want their query language back 
13th-May-2010 11:34 am
asstards
So, say you have a database that records the foo for bar. In mysql if you want to get the second top 5 foos, you would do this:
select foo from bar order by foo desc limit 5,5


Pretty straight forward, order by foo going down, start at 5, give me the next 5.

But we all know mysql is a dinky db, and real applications should use Oracle. In Oracle you would do this:

select foo from ( 
  select a.foo, ROWNUM rnum from ( 
    select foo from bar with order by foo desc
  ) a where ROWNUM <= 10 )
where rnum  >= 5;


Seriously. SERIOUSLY? I have to write two subselects to do something like this?? WHAT THE FUCK. Do I have to chisel it out of rock too, or can I press triangular impements into drying clay? Why the hell does the query parser not do this bullshit rather than leaving it for the poor sap writing the SQL. ARGHHHHH

ARGHH. AAAAAAAAAAAAAAAAAARGH.
Comments 
13th-May-2010 01:36 am (UTC)
Because Oracle sucks wet farts out of dead pigeons.

DB/2 (another non-dinky database you may have heard of) will happily accept your original query.
13th-May-2010 01:39 am (UTC)
Sadly I am limited to what the organisation provides.

The other sad thing is that the oracle page I used as a reference says that this is his "all time favourite" use of rownum. ... I have no words.
13th-May-2010 01:45 am (UTC)
Oh, I wasn't suggesting a change. That would be a project I wouldn't want any part of.

The writer of the reference page is obviously an Oracle fanboy who needs to be flogged.
13th-May-2010 01:47 am (UTC)
heheh :)
13th-May-2010 02:08 am (UTC)
Oracle fanboy who needs to be flogged

That's a tautology.
13th-May-2010 02:14 am (UTC)
Anonymous
Then there's activerecord3, which iirc has oracle drivers.

Bar.order('foo').skip(5).take(5)
13th-May-2010 02:16 am (UTC)
Sounds like a transcript of a checkers move :-)
13th-May-2010 03:15 am (UTC)
It's vastly more maintainable, especially when you get to really big queries.
13th-May-2010 03:53 am (UTC)
It would be.
This page was loaded Dec 13th 2017, 3:44 am GMT.