Re: "TOP N" limit in Oracle 9i sql query utilizing count(*)

bbcrock_at_gmail.com wrote:
> My query currently looks like this:
>
> select user_id, count(*) total
> from employeelog
> where event = 'LOGIN'
> group by user_id
> order by count(*), user_id
>
> I would like to only return the top ten employees logging into the
> system. I am currently returning the results and only displaying the
> Top Ten, but would like to limit the results via the database to save
> processing time.
>
> Utilizing ROWNUM in oracle (where rownum <= 10) only checks the first
> 10 records due to the behavior of count(*) and my results always come
> back:
>
> 173, 10
>
> I'm supposed to see something like
>
> 173, 45
> 12, 44
> 13, 44
> 18, 44
> 33, 43
> etc- just ten results
>
> Is there a way to do this quickly in Oracle utilizing aggregate
> functions? I'm drawing a blank.
>
> thanks,
>
> Don

Turkbear's solution will work in some/most cases but there are situations where it will not work. Take a look at the RANK function.

There are demos in Morgan's Library at www.psoug.org under RANK.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)