Look at the RANK() analytic functions in 8.1
There is a paper on my web-site.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
Simon wrote in message <902680F1Esimoninamecom_at_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 Thu Jan 11 2001 - 03:01:55 CST
