Re: How to get the TOP N records?

select D_CREDIT_LIMIT from (select -rownum r, D_CREDIT_LIMIT from (select count(*) c, D_CREDIT_LIMIT from dealer group by D_CREDIT_LIMIT ))
where r-10<(select min(r) from (select r, D_CREDIT_LIMIT from (select -rownum r, D_CREDIT_LIMIT from (select count(*) c, D_CREDIT_LIMIT from dealer group by D_CREDIT_LIMIT ))))
group by r, D_CREDIT_LIMIT
/

the query above should do the trick.

Don't be affraid to try. Maybe you can let the count(*) c part away, i haven't tried that.
If you want understand it it's better break it in peaces and see what the selects do. You can't use order by since the queries between the () are handled as views.

Hope it works. Let me know if you want.

Good luck,

Maiko

On 15 Mar 2000 16:41:00 GMT, "Dante Tang" <c8354710_at_comp.polyu.edu.hk> wrote:

>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
Received on Thu Mar 16 2000 - 13:32:43 CET