How to find the most frequently occurring value in a column in a SQL Server table

Recently, I have been coding T-SQL Stored Procedures in a SQL Server database when I ran across something which should be easy to do, but probably wasn’t, that ended up actually being easy.  Specifically, I had to find the most commonly occurring item in a column in a table.

For example if I had a People table with a Name column with the values: Paul, Dave, Paul.  Now, I want to find the value “Paul” because it occurs most frequently.

Obviously, my first thought was to do:

select Name, count(*)

from People

group by Name

order by count(*) desc

which would give me 2 rows:

Paul 2

Dave 1

Then, I could add a ‘top 1’ to the select to only get:

Paul 2


However, this is not what I want.  I don’t want the ‘2’.  So, I played around with the SQL a bit and came up with:

select top 1 Name

from People
group by Name
order by Count(*) desc

This SQL gave me exactly what I wanted: ‘Paul’

So, it actually turned out to be easy!  I just wanted to share this with everybody in case you run across the same situation and need a good solution.