Re: How to get the TOP N records?

Write a PL/SQL procedure, create a cursor with your query and in the FOR loop initialize a counter and stop at 5.

Use DBMS.OUTPUT to display lines.

Maiko wrote:

> 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 Wed Mar 22 2000 - 14:12:44 CET