I want to do the above in SQL Server 2008. Any ideas?
-
how do you want to handle months where the max value is shared by two days?geofftnz– geofftnz2011-06-16 03:56:36 +00:00Commented Jun 16, 2011 at 3:56
-
possible duplicate of SQL - fetch the row which has the Max value for a columnSaurabh Gokhale– Saurabh Gokhale2011-06-16 03:57:55 +00:00Commented Jun 16, 2011 at 3:57
Add a comment
|
1 Answer
Like this?
Setup:
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)
Query:
;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
Output:
Year Month Day Total
----------- ----------- ----------- -----------
2005 9 15 28
2005 10 31 5
2005 11 18 115