Re: sql query problem related to rownum. please help

techimadhu wrote:

> i need to do a top-n query after doing a sorting using order by. the
> query i am using is as below
>
> select * from
> (select * from table
> order by colA,colB)
> where rownum < 100
>
> i am having almost 1 lakh records in the table and because of the two
> levels of the query it is taking lot of time.
> i want to know is there any better way of achieving this like doing it
> in single query or something.

Take a look at the analytic functions. Specifically the use of RANK.

Tom Kyte has published a number of excellent demos using the analytics that can be found at http://asktom.oracle.com.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)