Re: order by, rownum and more

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 Received on Thu Aug 08 2002 - 14:20:11 CDT