How to find the following SQL query?

Question

there is a table in SQL database, called Players: Players (ID, name, age, gender, score) where ID is the primary key.

Now I want to write a query to find the following results: For each age, find the name and age of the player(s) with the highest score among all players of this age.

I wrote the following query:

SELECT P.name, P.age
FROM Players P
WHERE P.score = (SELECT MAX(P2.score) FROM Players P2)
GROUP BY P.age, P.name
ORDER BY S.age

However, the result of the above query is a list of players with the highest score among ALL players across all ages, not for EACH age.

Then I changed my query to the following:

SELECT P.name, P.age, MAX(P.score)
FROM Players P
GROUP BY P.age, P.name
ORDER BY P.age

However, the second query I wrote gives a list of players with each age, but for each age, there are not only the players with the highest score, but also other players with lower scores within this age group.

How should I fix my logic/query code?

Thank you!

Answer 1

You can use rank to do this.

select name, age
from (
SELECT *, 
rank() over(partition by age order by score desc) rnk
FROM Players) t
where rnk = 1

Answer 2

Your original query is quite close. You just need to change the subquery to be a correlated subquery and remove the GROUP BY clause:

SELECT P.name, P.age
FROM Players P
WHERE P.score = (SELECT MAX(P2.score) FROM Players P2 WHERE p2.age = p.age)
ORDER BY P.age;

The analytic ranking functions are another very viable method for processing this question. Both methods can take advantage of an index on Players(age, score). This also wants an index on Players(score). With that index, this should have better performance on large data sets.

Answer 3

You can try it also.

SELECT p.name, p.age, p.score
FROM players p
INNER JOIN
    (SELECT `age`, MAX(`score`) AS Maxscore
    FROM players
    GROUP BY `age`) pp 
ON p.`age` = pp.`age` 
AND p.`score` = pp.Maxscore;

Answer 4

Try it this will resolve your issue :

select p1.name,p1.age,p1.score from players p1 where p1.score = 
(SELECT max(score) from players where age = p1.age) group by p1.age;

If you will required all records having same maximum score : Then you will use this. I have tested both the query on my localhost.

SELECT p1.name,p1.age,p1.score FROM players p1 
WHERE p1.score IN (SELECT MAX(score) FROM players GROUP BY age)