Re: How to get the TOP N records?

rownum won't work because it only represents the order in which the rows where retrieved from the database. That's why it mixes up when you start ordering the rows.

Here is a solution I used for my table with books. I decided to have the top 7 prices (but I want to see 8 and more if the price of the eighth book equals the price of the seventh and so on.

  1 select id, price from books b1
  2 where 6 >= (select count(*) from books b2   3 where b2.price > b1.price)
  4 and b1.price is not null
  5* order by price desc
SQL> /        ID PRICE
--------- ---------

       55      43.5
       46      33.9
        4      33.5
       32     33.45
       63      26.5
       53      22.5
       23      20.2
       25      20.2

8 rows selected.

hopes this helps you
good luck
Olga Greep

Dante Tang wrote in message <01bf8e9c$af82f1e0$4f08849e_at_default>...
>I have a dealer table which contains dealer information including the
>dealer's credit limit. This table contains 10 tuples and the tuples is not
>sorted by the credit limit.
>
>How can I select 5 dealers whom has the first 5 top credit limit? I have
>tried the to include a rownum condition in the SQL. However, it does not
>work well. So I select the rownum and see what it is. I'm so surprise to
>discover that the rownum means the internal storage record number in the
>table. So it does not help in any means for my objective.
>
>Do you have any good idea on doing my tasks? Please give me a hand. Thanks~
>
>
>select rownum, d_credit_limit
>from dealer
>where rownum <= 10
>order by d_credit_limit desc;
>
> ROWNUM D_CREDIT_LIMIT
>---------- --------------
> 9 210000
> 5 34000
> 2 20000
> 8 15000
> 1 10000
> 10 10000
> 6 10000
> 7 10000
> 3 10000
> 4 8700
>--
>e-mail : c8354710_at_comp.polyu.edu.hk
>URL : http://www.comp.polyu.edu.hk/~c8354710
Received on Wed Mar 15 2000 - 20:11:19 CET