Re: selecting the nth largest values in a column

From: Myron Wintonyk <mwintony_at_med.ualberta.ca>
Date: Thu, 11 Jan 2001 15:20:46 +1000
Message-ID: <3A5D42AE.16751E04@med.ualberta.ca>

According to the books, the right answer is PL/SQL.

However, in Oracle 8i (and I think in Oracle 8), this will work. it looks extremely innefficient, however, I tried in on a 230,686,720 byte table with 2,585,101 rows (of course it had an index on THENUMBER). It executed in 0.06 seconds.

select VALUE, KEY

       from TABLE_1
       where KEY = ( select max( KEY ) from TABLE_1 )

union
select VALUE, KEY

       from TABLE_1
       where KEY = ( select max( KEY )
                        from TABLE_1
                        where KEY <> ( select max( KEY )
                                          from TABLE_1 ))

union
select VALUE, KEY

       from TABLE_1
       where KEY = ( select max( KEY ) from TABLE_1
                               where KEY <> ( select max( KEY )
                                                 from TABLE_1 )
                                 and KEY <> ( select max( KEY )
                                                 from TABLE_1
                                                 where KEY <> ( select max(
KEY )
                                                                    from

TABLE_1 ))); Of course, if the valye of "n" is high, the statment would become unruley and

PL/SQL IS in fact the answer ...

Simon wrote:

> 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 - 23:20:46 CST