Path: news.easynews.com!easynews!feedwest.aleron.net!aleron.net!sfo2-feed1.news.algx.net!allegiance!logbridge.uoregon.edu!news-west.eli.net!not-for-mail
Message-ID: <3D52C466.19DA563E@exesolutions.com>
From: Daniel Morgan <dmorgan@exesolutions.com>
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.misc
Subject: Re: order by, rownum and more
References: <Pine.OSF.4.43.0208081455040.12797-100000@grover.WPI.EDU>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 47
Date: Thu, 08 Aug 2002 19:20:11 GMT
NNTP-Posting-Host: 156.74.250.7
X-Complaints-To: yvonne.tracy@ci.seattle.wa.us
X-Trace: news-west.eli.net 1028834411 156.74.250.7 (Thu, 08 Aug 2002 13:20:11 MDT)
NNTP-Posting-Date: Thu, 08 Aug 2002 13:20:11 MDT
Organization: City of Seattle NewsReader Service
Xref: easynews comp.databases.oracle.misc:85335
X-Received-Date: Thu, 08 Aug 2002 12:17:22 MST (news.easynews.com)

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

As TurkBear says there is no such thing as row 6.

The only way to return the sixth row is to return the sixth row from the
result set so you would need to do something like this:


SELECT field, field, field, rownum XXX
FROM sometable

and then wrap it up as an in-line view as follows:

SELECT *
FROM (
   SELECT field, field, field, rownum XXX
   FROM sometable)
WHERE XXX = 6;

Daniel Morgan