CODEWITHSUNDEEP
sql
BaconCatBug
BaconCatBug

Reputation: 195

Find top result for each month from a table

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?

Upvotes: 0

Views: 547

Answers (1)

Scrub
Scrub

Reputation: 84

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

Upvotes: 1

Related Questions