获取具有列最大值的整个行 - SQL Server 2008

Question

我想在SQL Server 2008中完成上述操作，有什么好的方法吗？

- GayanSanjeewa

---

你想如何处理最大值被两天共享的月份？ - geofftnz

可能是重复的问题：SQL - 获取某列最大值所在的行 - Saurabh Gokhale

Answer 1

像这样吗？

设置：

declare @MyTable table(Year int, Month int, Day int, Total int)

insert @MyTable
values
    (2005, 9, 23, 12),
    (2005, 9, 26, 5),
    (2005, 9, 24, 1),
    (2005, 9, 15, 28),
    (2005, 9, 21, 1),
    (2005, 9, 13, 1),
    (2005, 10, 31, 5),
    (2005, 11, 18, 115),
    (2005, 11, 20, 1),
    (2005, 11, 11, 1),
    (2005, 11, 19, 1)

查询：

;with cte
as
(
    select *,
        row_number() over(partition by Year, Month order by Total desc) RowNumber
    from @MyTable
)
select Year, Month, Day, Total 
from cte
where RowNumber = 1

输出：

Year        Month       Day         Total
----------- ----------- ----------- -----------
2005        9           15          28
2005        10          31          5
2005        11          18          115

- Alex Aza