Re: order by, rownum and more

From: Christopher Beck <christopher.beck_at_oracle.com>
Date: Thu, 8 Aug 2002 15:26:40 -0400
Message-ID: <LEz49.13$wl2.180@news.oracle.com>

try:

select c1, c2, c3
  from ( select c1, c2, c3, rownum r

              from ( select c1, c2, c3
                          from T order by b ) )

 where r = 6

or using analytic functions

select c1, c2, c3
  from ( select c1, c2, c3, row_number() over ( order by b ) r

               from T )
 where r = 6

hope this helps.

chris.

--
Christopher Beck, Principal Technologist, Oracle Corporation,
christopher.beck_at_oracle.com
Beginning Oracle Programming,
http://www.amazon.com/exec/obidos/ASIN/186100690X



"Brian Murphy" <rudie_at_wpi.edu> wrote in message
news:Pine.OSF.4.43.0208081455040.12797-100000_at_grover.WPI.EDU...


> 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:26:40 CDT