Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!216.218.192.242!news.he.net!chekhov.conxion.net!news.oracle.com!not-for-mail From: "Christopher Beck" Newsgroups: comp.databases.oracle.misc References: Subject: Re: order by, rownum and more Lines: 61 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Thu, 8 Aug 2002 15:26:40 -0400 NNTP-Posting-Host: 138.1.120.89 X-Trace: news.oracle.com 1028834987 138.1.120.89 (Thu, 08 Aug 2002 12:29:47 PDT) NNTP-Posting-Date: Thu, 08 Aug 2002 12:29:47 PDT Xref: easynews comp.databases.oracle.misc:85336 X-Received-Date: Thu, 08 Aug 2002 12:24:20 MST (news.easynews.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@oracle.com Beginning Oracle Programming, http://www.amazon.com/exec/obidos/ASIN/186100690X "Brian Murphy" wrote in message news:Pine.OSF.4.43.0208081455040.12797-100000@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 > > > >