Find top result for each month from a table-CodePudding

I've got aggregate table filtered to 3 things

Month | Days | Total
202109 | 2  | 546
202109 | 3  | 9
202109 | 14 | 92
202108 | 2  | 666
202108 | 4  | 23

I wanna get new table that gives Month | Days | Total

But only returns the entry where it has the highest total for that month So desired result would give

Month | Days | Total
202109 | 2 | 546
202108 | 2 | 666

Any idea how I could go about doing this?

CodePudding user response：

select Month, Days, max(Total) 
from table1
group by Month

if this does now work as days must be included in group by use a temp table

select Month, max(Total) as Total into #temp 
from table1
group by Month

select b.Month, a.Days, b.total
from table1
inner join #temp b
on a.month = b.month and a.total = b.total