-2

I have the following tables:

Player_Attributes

player_api_id overall_rating date
30717 93 '2007'
30829 93 '2007'
30893 91 '2008'
30657 91 '2008'

Player

player_api_id player_name
30717 Player_A
30829 Player_B
30893 Player_C
30657 Player_D

The task is to get the MAX(overall_rating) of each year and the player_name associated with it via the player_api_id. The following code:

WITH CTEMax_Rating AS(
            SELECT SUBSTRING(date,0,5) AS Thedate, 
                   MAX(overall_rating) AS MaxRating
            FROM Player_Attributes
            GROUP BY SUBSTRING(date,0,5)
            )
            SELECT PAT.player_api_id, CTEMax_Rating.MaxRating,
                   CTEMax_Rating.Thedate, PYR.player_name 
            FROM CTEMax_Rating
            INNER JOIN 
                Player_Attributes AS PAT
            ON
                CTEMax_Rating.MaxRating = PAT.overall_rating
            AND
                CTEMax_Rating.Thedate = SUBSTRING(PAT.date,0,5)
            INNER JOIN 
                Player AS PYR
            ON 
                PAT.player_api_id = PYR.player_api_id
            ORDER BY CTEMax_Rating.Thedate

Gives this output:

player_api_id | overall_rating |  date  | player_name  
30717         |      93        | '2007' | Player_A
30829         |      93        | '2007' | Player_B

30893         |      91        | '2008' | Player_C
30657         |      91        | '2008' | Player_D

30657         |      91        | '2009' | Player_D

The thing is, how can I tweak the code to return a single occurrence since some players are tied with a MAX(overall_rating)?

The output I'd like is something like this:

player_api_id | overall_rating |  date  | player_name  
30717         |      93        | '2007' | Player_A  

30893         |      91        | '2008' | Player_C  

30657         |      91        | '2009' | Player_D

Thanks in advance.

OmarZ
  • 1
  • 2
  • 1
    This doesn't make sense to me. Your sample data has only a single player in each year. And your desired output has values that don't exist in your sample data. Where do the first two columns of data come from? – Sean Lange Jun 02 '22 at 16:51
  • That is just an extract, the tables have many more players. The two columns of data come from Player_Attributes(PAT).player_api_id and Player_Attributes(CTEMax_Rating).overall_rating. – OmarZ Jun 02 '22 at 16:59
  • OK. So your sample data and expected output don't match up. This makes it really difficult for others to help because we don't really know what you want as output. – Sean Lange Jun 02 '22 at 17:03
  • 1
    Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – SMor Jun 02 '22 at 17:34
  • Thank you very much for the link it did help me, this comment in particular did the trick: https://stackoverflow.com/a/48412942/19250129 – OmarZ Jun 02 '22 at 19:12

0 Answers0