| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ROWNUM question ???
Janet Linsy wrote:
>
> Hi,
>
> I have a table like this:
>
> SQL> select * from test;
>
> ID PRICE
> ---------- ----------
> 1 1
> 1 3
> 1 4
> 1 5
> 1 6
>
> I need to get the second largest price and I was using
> the ROWNUM.
>
> For the following statement, the result makes sense:
> SQL> select * from test where rownum < (select
> count(*) -1 from test);
>
> ID PRICE
> ---------- ----------
> 1 1
> 2 3
> 3 4
>
> But the rest twos didn't return anything:
>
> SQL> select * from test where rownum=(select count(*)
> -1 from test); -- supposed to return 5 ???
>
> no rows selected
>
> SQL> select * from test where rownum > (select
> count(*) -1 from test); -- supposed to return 6 ???
>
> no rows selected
>
> I vaguely remember that ROWNUM only works in < case,
> why is that? Thank you!
Janet,
The reason is that it is computed on the fly as rows are returned (i.e. pass all filters). Rownum=1 works, but rownum=2 cannot, since to have a rownum value of 2 you should have first displayed a rownum 1. Catch 22. < or <= works, for the same reason.
To answer your question, you have several ways to do it.
I have a test table slightly different from yours :
SQL> select * from test;
ID PRICE
---------- ----------
1 1.5
2 1.8
3 2
4 1.9
5 1.3
The rownum is computed BEFORE any ORDER BY :
1 select rownum, id, price
2* from test
SQL> /
ROWNUM ID PRICE
---------- ---------- ----------
1 1 1.5
2 2 1.8
3 3 2
4 4 1.9
5 5 1.3
1 select rownum, id, price
2 from test
3* order by price desc
SQL> /
ROWNUM ID PRICE
---------- ---------- ----------
3 3 2
4 4 1.9
2 2 1.8
1 1 1.5
5 5 1.3
However, you can cheat by having the ORDER BY performed inside an
in-line view :
1 select rownum, x.id, x.price
2 from (select id, price
3 from test 4* order by price desc) x
1 3 2
2 4 1.9
3 2 1.8
4 1 1.5
5 5 1.3
By nesting one degree deeper, you can answer your question (well,
fairly) easily :
1 select y.id, y.price
2 from (select rownum price_rank, x.id, x.price
3 from (select id, price 4 from test 5 order by price desc) x) y6* where y.price_rank = 2
4 1.9
Here, the rownum has been computed on the fly, but INSIDE the least nested in-line view, so it appears as 'static' data at the outside level and then = or > works.
Another way to do it is this :
1 select x.id, x.price
2 from (select id, price
3 from test 4 order by price desc) x 5 where x.price < (select max(price) 6 from test)
4 1.9
which is likely to be more efficient, especially if PRICE is indexed.
I have tried to have a go with the RANK() analytical function, but without much success :-(.
HTH,
Stephane Faroult
Oriole Ltd
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Sat Feb 09 2002 - 04:28:42 CST
 |
 |