3

Sample

I want to do the above in SQL Server 2008. Any ideas?

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
GayanSanjeewa
  • 366
  • 3
  • 13
  • how do you want to handle months where the max value is shared by two days? – geofftnz Jun 16 '11 at 03:56
  • possible duplicate of [SQL - fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/sql-fetch-the-row-which-has-the-max-value-for-a-column) – Saurabh Gokhale Jun 16 '11 at 03:57

1 Answers1

10

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
Alex Aza
  • 76,499
  • 26
  • 155
  • 134