SwimMaster
SwimMaster

Reputation: 379

sql select max for each grouped column and rest of row

I have a table in mysql

name    year    data
a       1        1
b       1        2 
c       2        3
a       2        4
c       3        5

For each year I need the max(data), year, and name associated with that max.

ive tried

select max(data), name , year from table group by year; however I do not have access to name.

Thank you in advance.

Upvotes: 0

Views: 21

Answers (1)

Srinivas V.
Srinivas V.

Reputation: 321

I think you can try something like below

select name, data, year from table A
join (select max(data) data, year from table a
group by year) B on A.data = B.data and A.year = B.year

Upvotes: 1

Related Questions