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
SQL> /
ROWNUM ID PRICE
---------- ---------- ----------
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) y
6* where y.price_rank = 2
SQL> /
ID PRICE
---------- ----------
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)
7* and rownum = 1
SQL> /
ID PRICE
---------- ----------
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
