SQL 选择最常见的记录

Question

我有下表:

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   |
+----+------+-------+

我想从特定组中选择前 20 个不同的名称，并按该组中最常见的名称排序。此示例中第 1 组的结果将返回 a b c (a - 3 次出现，b - 2 次出现，c - 1 次出现)。

谢谢。

Answer 1

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