Hi Guys,
If I have a Staff table that denotes staff JobDescriptions, how do I go about pulling out just the most commonly occurring JobDescription row(s)? Currently the table looks like:
JobDescription |
---|
Sysadmin |
Sysadmin |
Sysadmin |
Sales Manager |
Sales Manager |
Middle Manager |
Middle Manager |
Middle Manager |
By doing the following:
Select Staff.JobDescription,count(Staff.JobDescription) as JobCount from HR.Staff
group by Staff.JobDescription
order by JobCount desc;
I can grab a table like so:
JobDescription | JobCount |
---|---|
Sysadmin | 3 |
Middle Manager | 3 |
Sales Manager | 2 |
But my desired output is the single most commonly occuring JobDescription (which in this example has two both with a value of '3'):
JobDescription | JobCount |
---|---|
Sysadmin | 3 |
Middle Manager | 3 |
Hopefully this isn't immediately obvious... I'm guessing it's going to involve a nested SELECT, in which case I'll forgive myself for feeling so lost.
Thanks!