SQL Server Performance Forum – Threads Archive

Query optimisation

Hi,
I have a table called Department which the following records Name Department Sex
Sara D1 M
Amp D1 F
wH D2 F
K2 D3 F
Mar D3 F
Clo D3 F
Plk D4 F I want to get the department where Maximum no of females are working. I used the query
select t1.dept from(
SELECT DEPT,COUNT(Sex) AS C FROM Department WHERE SEX=’F’
GROUP BY DEPT ) as t1 , (SELECT DEPT,COUNT(Sex) AS C FROM Department WHERE SEX=’F’
GROUP BY DEPT) as t2
group by t1.dept, t1.c having t1.c=max(t2.c)
which produced D3 I want to know whether there is any simpler way to do this? Madhivanan

---

select top 1 dept
from department
where sex = ‘F’
group by dept
order by count(sex) desc if there are multiple dept’s with the same maximum number of females then it will arbitrarily choose one of them.
Cheers
Twan

---

Thanks Twan. Yours is simple and fast. Madhivanan

---

]]>