Re: ROWNUM question ???

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Sat, 09 Feb 2002 02:28:42 -0800
Message-ID: <F001.0040B223.20020209021817@fatcity.com>

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: 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