Is there a better way to find the max count in a table

Question

select NV.PHG
From Nhanvien NV
Group by NV.phg
Having count(nv.Manv) >= all (select count(NV.MANV from nhanvien nv group by nv.MANV))

I'm finding a better way to find the 'max count' NV of a PHG ( in this example ) . I think, we meet this case all the time when we do SQL, i should've a better way . Thanks for reading this :)

Answer 1

To find the group with the largest number of members you can use this:

SELECT TOP(1) phg, COUNT(*) AS count
FROM Nhanvien
GROUP BY phg
ORDER BY COUNT(*) DESC