Re: selecting the nth largest values in a column

Hi,

in Oracle 8i (as i know not in 8) you can use order by in subquery like

select value, key from (select value, key from table order by value) where rownum < 4;

May be a bit faster (and easier to maintain) than Myrons way. However his sql will also work in Oracle 7 or 8 :-)

hth
Stephan

Myron Wintonyk schrieb:

> 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 Thu Jan 11 2001 - 09:27:45 CST