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

From: <bbcrock_at_gmail.com>
Date: 18 Nov 2005 11:32:29 -0800
Message-ID: <1132342349.807119.92000@o13g2000cwo.googlegroups.com>

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 Received on Fri Nov 18 2005 - 13:32:29 CST