抓住每个组中具有最高价值的行？

一尘不染

抓住每个组中具有最高价值的行？

sql

我有看起来像这样的数据：

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"

阅读 154

2021-03-08

共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。它们是关键字，将导致头痛和令人伤心。

2021-03-08