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

Question

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?

Gordon Linoff · Accepted Answer · 2020-05-20 20:08:35Z

1

Use distinct on:

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

answered May 20, 2020 at 20:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicotops Over a year ago

Thanks a lot, it was actually this simple! I think I got confused by the MAX value that I was looking for.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related