I have data that looks like this:
我的数据是这样的:
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"
How can I query this data to get for each group the most popular (by count) color. So the result would look like this:
如何查询此数据以获取每个组最流行(按计数)的颜色。结果是这样的
Result
Group: "A", Color: "Blue"
Group: "B", Color: "Purple"
2 个解决方案
#1
6
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
As an aside, don't name a field GROUP
, or COUNT
. They are keywords and will lead to headaches and heartbreak.
顺便说一句,不要命名字段组或计数。它们是关键词,会导致头痛和心碎。
#2
0
This should also work:
这也应该工作:
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
#1
6
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
As an aside, don't name a field GROUP
, or COUNT
. They are keywords and will lead to headaches and heartbreak.
顺便说一句,不要命名字段组或计数。它们是关键词,会导致头痛和心碎。
#2
0
This should also work:
这也应该工作:
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