select top based on multiple fields MS Access

Question

table structure

Fields:

Team, Season, Rating

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

ie:

Team     Season Rating

Clemson 2019    52.1

Alabama 2018   53.6

Alabama  2017    48.5

...

Michigan 1948   35.6

---

David E. McKnight

Answer 1

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)

Answer 2

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