SQL 选择其他列是组最大值的列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14254822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select column value where other column is max of group
提问by Richard Todd
I am trying to select two columns into a table (ID and state). The table should show the state with the maximum value for each ID. I've tried a few other examples but nothing seems to work.
我试图在表中选择两列（ID 和状态）。该表应显示每个 ID 具有最大值的状态。我尝试了其他一些示例，但似乎没有任何效果。
Original data structure:
原始数据结构：
ID state value (FLOAT)
1 TX 921,294,481
1 SC 21,417,296
1 FL 1,378,132,290
1 AL 132,556,895
1 NC 288,176
1 GA 1,270,986,631
2 FL 551,374,452
2 LA 236,645,530
2 MS 2,524,536,050
2 AL 4,128,682,333
2 FL 1,503,991,028
The resulting data structure should therefore look like this:
因此，生成的数据结构应如下所示：
ID STATE (Max Value)
1 FL
2 AL
Florida and Alabama having the largest values in their ID groups.
佛罗里达州和阿拉巴马州在其 ID 组中具有最大值。
Any help would be greatly appreciated on this. I did find a SO answer herealready, but could not make the answers work for me.
任何帮助将不胜感激。我确实已经在这里找到了一个 SO 答案，但无法使答案对我有用。
回答by Damien_The_Unbeliever
For SQL Server (and other products with windowed functions):
对于 SQL Server（以及其他具有窗口函数的产品）：
SELECT *
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY value desc) as rn
FROM
UnnamedTable
) t
WHERE
t.rn = 1
回答by Jacco
A solution, based on the assumption that valueis numeric:
一个基于value数字假设的解决方案：
SELECT
[ID],
[State],
[Value]
FROM
(
SELECT
[ID],
[State],
[Value],
Rank() OVER (PARTITION BY [ID] ORDER BY [Value] DESC) AS [Rank]
FROM [t1]
) AS [sub]
WHERE [sub].[Rank] = 1
ORDER BY
[ID] ASC,
[State] ASC
If multiple States with the same IDhave the same Value, they would all get the same Rank. This is different from using Row_Number, which return unique row numbers, but the order is chosen arbitrarily. (See also: SQL RANK() versus ROW_NUMBER())
如果State具有相同的多个s 具有ID相同的Value，则它们都会得到相同的Rank。这与 using 不同Row_Number，后者返回唯一的行号，但顺序是任意选择的。（另请参阅：SQL RANK() 与 ROW_NUMBER()）
回答by Taryn
You can use a subquery to get this result:
您可以使用子查询来获得此结果：
select t1.id, t1.[state] MaxValue
from yourtable t1
inner join
(
select id, max(value) MaxVal
from yourtable
group by id
) t2
on t1.id = t2.id
and t1.value = t2.maxval
order by t1.id
