Table

I have the following table above. Each year is supposed to have 2 rows one for lgID = NL and one for lgIG = AL. I want to break any ties in the HR column based on the RBI value. Higher RBI stays and lower gets dropped.

so for example the year 1932 in NL player ottme01 should be dropped because he has a lower RBI value than kleinch01.

for 1934 in NL colliri02 should be dropped because he has a lower rbi value than ottme01

trying to do this in mysql

Id like to convert this

into this

CodePudding user response：

We can use ROW_NUMBER here:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY yearID, IgID
                                   ORDER BY HR DESC, RBI DESC) rn
    FROM yourTable t
)

SELECT *
FROM cte
WHERE rn = 1;