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.