Multiple Categories in a query - Part 2

[Paid The Umpire]

Some nice people gave me the following query to create a list of the
highest score in each of the Categorys overall for my sporting club.

SELECT *
FROM [Your Table] as A
WHERE A.Score =
(SELECT MAX(Score)
FROM [Your Table] as Tmp
WHERE Tmp.Category = A.Category)

I'm now trying to find a query like the above mentioned, except that I
now want to get the highest in each category, for each "Grade".

Any further ideas?

 

[John Spencer]

Field and table names are a help when making suggestions.

Use two queries.
First query saved as qMaxCategoryGrade
SELECT Category, Grade, Max(Score) as HighScore
FROM [Your Table]

Use that saved query along with your original table and join on Category, Grade,
and Score
SELECT A.*
FROM [Your table] as A
INNER JOIN qMaxCategoryGrade as Q
ON A.Category = Q.Category AND
A.Grade = Q.Grade AND
A.Score = Q.HighScore


If your table and field names have no spaces or other non alpha-numeric
characters this can be done in one query.

SELECT A.*
FROM YourTable as A
INNER JOIN
(
SELECT Category
, Grade
, Max(Score) as HighScore
FROM YourTable
) as Q
ON A.Category = Q.Category AND
A.Grade = Q.Grade AND
A.Score = Q.HighScore