order by, rownum and more

Hello,

I'm currently trying to do some order based queries with oracle. Yes, I know, relational databases aren't order sensitive, but I'm supplying a order as a column value.

I am attempting to order by my columns, then return the 6th row, with oracle's ROWNUM. But so far, I can only get the appropriate result if my query resembles the following:

(select * from (select * from my_tab order by b) where rownum <=6)
  minus
(select * from (select * from my_tab order by b) where rownum <6);

This will return the 6th row. But it must be quite expensive. Any idea on how I could speed up this query, while still retrieving the proper result?

I've heard mention of oracle extensions that mention order/time/rank, but haven't found any documentation. Perhaps that would help me? Anyone have a link, or info on that?

Thanks.

brian Received on Thu Aug 08 2002 - 14:00:13 CDT