Improve query to get most games won

Question

Here is the problem as follows:

team	season	games_won
San Francisco Giants	2010	92
San Francisco Giants	2011	86
San Francisco Giants	2012	94
San Francisco Giants	2013	76
San Francisco Giants	2014	88
Los Angeles Dodgers	2010	80
Los Angeles Dodgers	2011	82
Los Angeles Dodgers	2012	86
Los Angeles Dodgers	2013	92
Los Angeles Dodgers	2014	94

Write the SQL to generate the following output, where the second column is the season in which each team had the most wins. The Giants & Dodgers are shown for illustrative purposes below, but the SQL should pull all teams at once:

I have two methods to solve this:

Method 1

WITH cte_wins
 AS (SELECT team,
            Max(games_won) AS most_wins
     FROM   table_wins
     GROUP  BY team)
SELECT table_wins.team,
       table_wins.season AS season_with_most_wins
FROM   table_wins
       JOIN cte_wins
         ON cte_wins.team = table_wins.team
            AND cte_wins.most_wins = table_wins.games_won

Method 2

SELECT table_wins.team,
       table_wins.season AS season_with_most_wins
FROM   table_wins
       JOIN (SELECT team,
                    Max(games_won) AS most_wins
             FROM   table_wins
             GROUP  BY team) AS sub
         ON sub.team = table_wins.team
            AND sub.most_wins = table_wins.games_won

Method 3

Is there a better method to solve this question? I am wondering if my approach is just bad and maybe I should try something else. Any and all feedback would be welcomed.

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Jan 17 '22 at 18:56
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jan 18 '22 at 09:24

score 0 · Answer 1 · answered Jan 17 '22 at 19:59

I think you are probably really asking for which team had most wins PER SEASON, not just the most wins... For that, you might be best with over/partition something like

select
        PQ.*
    from
    ( select
            TW.season,
            TW.Team,
            TW.Games_Won,
            RANK() OVER ( PARTITION BY TW.Season 
                            ORDER BY games_won desc ) SeasonPlace
        FROM 
            __Team_Wins TW ) PQ
    where
        PQ.SeasonPlace = 1

The inner Pre-Query (PQ alias) pulls all rows and pre-sorts them by the Season, and within each season, ORDERS based on games_won descending, thus putting the highest games in first position. The RANK(), thus becomes a sequential 1, 2, 3, etc for the "SeasonPlace" column result.

So now, that becomes the inner query, and carries all the other fields along for the ride. The outer query then applies a WHERE clause getting only the team and all its details where they were in first place per each respective season.

Improve query to get most games won

1 Answers1