Query to only bring back Maximum of a Count field

[psharma1]

I have a SQL statement (below) for SQL Server 2008 DB that returns a Division, a Dept Code, and a count of employees grouped by the Division & Dept Code.
All I want to display is the MAX count, so a sample of my SQL is:

Divison Dept_Code Count_Emp
1011 100 1
1011 101 122
1011 123 1
1011 198 1
1011 199 1
1012 100 3
1012 101 36
1012 123 2
1012 198 1


But all I want to display is:

Division Dept Count_Emp
1011 101 122
1012 101 36


select di.divId Division, Dpt.Dept_Code, count(Emp.Emp_id)
from division di, Department Dpt, Employee Emp
where Emp.DIVID = di.DIVID and
Emp.Dept_Code = Dpt.Dept_Code and
EMP.status = 'A'
group by di.divId, Dpt.Dept_Code
order by 1,2

How can this be acheived? I've applied all of my SQL skills but can't figure this out. Will this need multiple nested queries or a Having Clause would work?

Please help.

Thanks!

 

[markros]

SQL 2005 and up:

;with cte as (select *, row_number() over (partition by Division order by Count_Emp DESC) as Rn
from Results)

select * from cte where Rn = 1

For details check

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library

 

[psharma1]

Hi Markros,

I'm having problem with paranthesis if I try to include the original query in place of "Results" in your response above.

Do I have to create a Temp table first and then use it in your query or is it possible to do it all together in 1 query.

Please elaborate and thanks for your response!

PS

 

[markros]

You may try

;with cte as (Original query goes here),
cte2 as (select *, row_number() ... from cte)

select * from cte where Rn = 1

PluralSight Learning Library

 

[psharma1]

Thanks Markros, this worked! This is going to be very useful for me.

Appreciate your help!
PS