select top based on multiple fields MS Access RRS feed

  • Question

  • table structure


    Team, Season, Rating

    Would like to query to list top 1 [Rating] team for each [Season]


    Team     Season Rating

    Clemson 2019    52.1

    Alabama 2018   53.6

    Alabama  2017    48.5


    Michigan 1948   35.6

    David E. McKnight

    Monday, August 31, 2020 12:54 PM

All replies

  • That would be a Totals query:

    SELECT Team, Season, Max(Rating) AS TopRating
    FROM [TableName]
    GROUP BY Team, Season

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Monday, August 31, 2020 1:06 PM
  • Try this:

    SELECT Team, Season, Rating
    FROM Ratings AS R1
    WHERE Rating =
         (SELECT MAX(Rating)
          FROM Ratings AS R2
          WHERE R2.Season = R1.Season)
    ORDER BY Season DESC;

    I've assumed for this example that that the table is named Ratings.  It works by the outer query being restricted to the team whose rating is the highest (MAX) for each season as returned by the subquery.  Each instance of the Ratings table is differentiated by the aliases R1 and R2.

    Ken Sheridan, Stafford, England

    Monday, August 31, 2020 5:39 PM