How to find the most frequently repeated column?

Question

ID  UserID LevelID
1   1       1
2   1       2
3   1       2
4   1       2
5   2       1
6   2       3
7   3       2
8   4       1
9   4       1

The query should return: LevelID: 1 (3 times) - the LevelID column that is most frequently repeated by different Users (UserID).

I have the following query:

SELECT LevelID, COUNT(LevelID) AS 'Occurrence' 
FROM 
(
  SELECT DISTINCT * FROM
  (
    SELECT UserID, LevelID
    FROM SampleTable
  ) cv
) levels
GROUP BY LevelID
ORDER BY 'Occurrence'  DESC

Which returns:

LevelID Occurence
1        3      
2        2      
3        1  

But it doesn't let me to add LIMIT 1; at the bottom to retrieve the first top row of the selection. What's wrong with the query?

Answer 1

There is no need for these several levels of nesting. Consider using aggregation, count(distinct ...), ordering the results and using a row-limiting clause to keep the top record only:

select top(1) levelID, count(distinct userID) cnt
from mytable
group by levelID
order by cnt desc

If you want to allow possible top ties, then use top (1) with ties instead of just top (1).