SQL Select top frequent records

sqlsql-server

I have the following table:

Table
+----+------+-------+
| ID | Name | Group |
+----+------+-------+
| 0  |   a  |   1   | 
| 1  |   a  |   1   | 
| 2  |   a  |   2   |
| 3  |   a  |   1   |
| 4  |   b  |   1   |
| 5  |   b  |   2   |
| 6  |   b  |   1   |
| 7  |   c  |   2   |
| 8  |   c  |   2   |
| 9  |   c  |   1   |
+----+------+-------+

I would like to select top 20 distinct names from a specific group ordered by most frequent name in that group. The result for this example for group 1 would return a b c (
a – 3 occurrences, b – 2 occurrences and c – 1 occurrence).

Thank you.

Best Solution

SELECT TOP(20) [Name], Count(*) FROM Table
WHERE [Group] = 1
GROUP BY [Name]
ORDER BY Count(*) DESC
Related Question