PLSQL help

SteamTrean

Hi,

I need some PLSQL code that will find the top ten employee numbers that appear most often in an incident table. The incident table has a number of fields including dates, times, description... It also holds the ECode of the person who logged it. I need to find the top ten ECodes.. i.e. a top ten list of Employees, where no. 1 is the Employee that has logged the most incidents.

Can anyone help?

Thanks in advance.

chatterbox

Havent Tested the below code, but its pretty close what you want.

select distinct(ecode) ECODE, count(1) NUMBER_OR_ENTRIES
from TABLE_NAME
group by ecode
order by count(ecode) desc

if you want to enter more conditions, stick in a where clause

select distinct(ecode) ECODE, count(1) NUMBER_OR_ENTRIES
from TABLE_NAME
WHERE condition
group by ecode
order by count(ecode) desc

pH

As for the 'top 10' - if you're using PLSQL you can obviously open a cursor and stop after 10 rows, but it is possible to do it in SQL using ROWNUM.

ROWNUM works on the base unsorted rows, so you need to add an outer select:

[B]SELECT * FROM ([/B]
select distinct(ecode) ECODE, count(1) NUMBER_OR_ENTRIES
from TABLE_NAME
WHERE condition
group by ecode
order by count(ecode) desc
[B]) WHERE ROWNUM <= 10[/B]

(Based on chatterbox's original SQL statement)

SteamTrean

Great help thanks.