抓住每个组中具有最高价值的行? [英] Grabbing the row with the highest value from each group?
本文介绍了抓住每个组中具有最高价值的行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有如下数据:
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"
推荐答案
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
.它们是关键字,将导致头痛和伤心欲绝.
As an aside, don't name a field GROUP
, or COUNT
. They are keywords and will lead to headaches and heartbreak.
这篇关于抓住每个组中具有最高价值的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文