selecting the nth largest values in a column

From: Simon <simon.dobner_at_i-nospam-name.com>
Date: 11 Jan 2001 03:53:11 GMT
Message-ID: <902680F1Esimoninamecom@10.1.1.251>

Hi,

I am trying to retrieve rows from a table for the n largest values of column x.

ie, if the table contains the following data:

KEY VAL
---------- -----

         1 aaa                              
         2 bbb                              
         4 ddd                              
         6 fff                              
         7 ggg                              
         8 hhh                              
         9 iii                              
        10 jjj                              
        26 zzz                              

I want to return the rows for the three largest values of val (ie, where key = 9,10 & 26)

Now, the following SQL works fine when there are only nine rows in the table

SQLWKS> select key,val
     2>  from table_1 a 
     3>  where 3 >= ( select count(*)  
     4>               from table_1 b
     5>               where b.val > a.val
     6>               or  ( b.val = a.val
     7>                     and  b.rowid >= a.rowid) 
     8>             ); 
KEY        VAL                              


---------- ---------------------------------

         9 iii                              
        10 jjj                              
        26 zzz                              

3 rows selected.

but does not appear to be efficient enough to finish this millenium when there are about two million rows in the table.

My question is this :

Does anyone have a better way of doing this, or suggestions for improving what I have ?

TIA Simon Received on Wed Jan 10 2001 - 21:53:11 CST