From: TurkBear Newsgroups: comp.databases.oracle.misc Subject: Re: order by, rownum and more Date: Thu, 08 Aug 2002 14:14:58 -0500 Reply-To: jgreco1@mn.rr.com Message-ID: References: X-Newsreader: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 46 X-Authenticated-User: 86802158 X-Comments: This message was posted through Newsfeeds.com X-Comments2: IMPORTANT: Newsfeeds.com does not condone, nor support, spam or any illegal or copyrighted postings. X-Comments3: IMPORTANT: Under NO circumstances will postings containing illegal or copyrighted material through this service be tolerated!! X-Report: Please report illegal or inappropriate use to You may also use our online abuse reporting from: http://www.newsfeeds.com/abuseform.htm X-Abuse-Info: Please be sure to forward a copy of ALL headers, INCLUDING the body (DO NOT SEND ATTACHMENTS) Organization: Newsfeeds.com http://www.newsfeeds.com 73,000+ UNCENSORED Newsgroups. Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!216.218.192.242!news.he.net!news-out.spamkiller.net!propagator2-maxim!propagator-maxim!news-in.spamkiller.net!feed.newsfeeds.com!goliath2.newsgroups.com!not-for-mail Xref: easynews comp.databases.oracle.misc:85334 X-Received-Date: Thu, 08 Aug 2002 12:12:18 MST (news.easynews.com) There is still a semantic problem here..There is no such thing as THE 6th record.. That could be any record in the table depending on how you structure the query that retrieves it... If the 6th row ( or any particular row - since there is no 6th row in the stored data) is significant why not just add a rownumber field and populate it when it is inserted? then you could access it, for whatever reason, directly: where rownumber = 6; Brian Murphy wrote: >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 > > > -----------== Posted via Newsfeed.Com - Uncensored Usenet News ==---------- http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----