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

Ok, ( not knowing the Oracle version, this is the old method - newer version can use the analytic functions)

You need an in-line query - something like: Select * from (
select user_id, count(*) total
from employeelog
where event = 'LOGIN'
group by user_id
order by count(*) desc)
where rownum < 11

Example:
SQL> select * from (
  2 select job_cd,count(empl_nbr) from mydata   3 where empL_stat_cd = 'A' group by job_cd order by count(empl_nbr) desc )   4 where rownum < 11;

JOB_CD COUNT(EMPL_NBR)
------ ---------------

003685            1070
003676             464
003677             461
000556             172
000994             166
000997             141
000927             103
002606              88
003584              82
003817              75

hth, Received on Fri Nov 18 2005 - 13:49:50 CST