从每个组中获取具有最高值的行？

Question

我有这样的数据：

Table
Group: "A" , Color: "Blue", Count: "400"
Group: "A" , Color: "Green", Count: "3"
Group: "A" , Color: "Yellow", Count: "6"
Group: "A" , Color: "Red", Count: "1"
Group: "B" , Color: "Purple", Count: "243"
Group: "B" , Color: "Green", Count: "2"
Group: "B" , Color: "Yellow", Count: "7"

如何查询数据以获取每个组中最受欢迎（按计数）的颜色。因此，结果将如下所示：

Result
Group: "A", Color: "Blue"
Group: "B", Color: "Purple"

- sooprise

Answer 1

SELECT Group, Color
FROM MyTable t
INNER JOIN (SELECT Group, Max(CAST(Count as int)) as Ct
            FROM MyTable
            GROUP BY Group) Sub
   ON sub.group = t.group
   AND sub.ct = t.count

作为一点额外提示，不要将字段命名为GROUP或COUNT。它们是关键字，会导致麻烦和悲伤。

- JNK

---

+1，t.count 是 varchar 类型。在与 sub.ct 进行比较时需要进行转换。 - aF.

@aF。- 很好的观点。实际上，我应该在子查询中进行CAST，因为如写作的40 > 300。 - JNK

如果您有两个蓝色400，则会返回两条记录。最好将最终结果去重。 - sam yi

Answer 2

这个也应该可以工作：

SELECT    t.Group,
          t.Color,
          t.Count
FROM     (SELECT   Group,
                   Color,
                   Count,
                   ROW_NUMBER() OVER(PARTITION BY Group ORDER BY CAST(Count AS INT) DESC) AS num
          FROM Table) AS t
WHERE     t.num = 1

- pistipanko

---

RANK()可能是更好的选择，因为可能有多个颜色具有相同的最大计数。 - Andriy M

有什么区别呢？毕竟，组中只取第一行。您可以调整ORDER BY子句以为情况下的颜色添加优先级，当计数相同时。 - pistipanko

如果您认为（“仅采用组中的第一行”）是正确的假设，那么ROW_NUMBER()当然是一种方法。但是，如果OP的意思是包括每个组中前count次出现的所有情况，即使某些组包含多个情况，那么ROW_NUMBER()将给您不完整的结果。 - Andriy M