How to get the MAX value accross two columns in SQL/PostgreSQL

user_13414258 0
Viewed 36

So I'm working on a table in PostgreSQL about the pitchers' stats in Major League Baseball. The table contains lots of data but I'm mostly interested by those 3 columns: 

playerid | yearid | strike_outs
-------------------------------
  id1    |  2019  |     1
  id1    |  2018  |     20
  id2    |  2019  |     14
  id2    |  2018  |     8
  id3    |  2019  |     27
  id3    |  2018  |     12

I would like to get, per year, the most strikes-outs scored this year and the player's name beside, so something like this:

yearid | playerid | strike_outs
-------------------------------
  2019 |    id3   |     27
  2018 |    id1   |     20

I managed to get the yearid and the most strike-outs scored this year (without info on the playerid), or the playerid and the most strike-outs they ever scored, but cannot get all the info at once. I tried with DISTINCT ON or subquery, but I cannont get the desired result.

And with the following query, I get all results, but not the top scorer pear year:

SELECT yearid,MAX(so),playerid
FROM pitching
GROUP BY yearid,playerid
ORDER BY yearid DESC,MAX(so) DESC
;

Could somebody give me a hint?

postgresql sql
1 Answers

Use distinct on:

select distinct on (yearid) p.*
from pitching p
order by yearid, so desc;
user_11440350
Related