I have 3 tables with name USER, SCORE and LEVEL as given below.
USER +----+---------------+ | id | name | +----+---------------+ | 1 | Mr Alexander | | 2 | Ms. Duma | +----+---------------+ LEVEL +----+------+ |id | seq | +----+------+ | 1 | 1 | | 2 | 2 | +---+-------+ SCORE +----+---------+------------+-------+ |id | user_id | level_id | score | +----+---------+------------+-------+ | 1 | 1 |1 |3 | | 2 | 1 |2 |2 | | 3 | 1 |1 |4 | | 4 | 1 |1 |2 | | 5 | 2 |1 |3 | +---+----------+------------+-------+
From these tables, I’m trying to prepare a scoreboard, in which each user will be arranged based on their level, and score. And for each user, only highest level and corresponding highest score in that level will be shown.
I have written a JOIN query as below for this purpose.
SELECT user.name, MAX(level.seq) as level, MAX(score.score) as score FROM api_score score JOIN api_level level ON level.id = score.level_id JOIN api_user user ON user.id = score.user_id group by user.id, level.seq ORDER BY level DESC, score DESC;
But, this query is returning all levels highest score in each level. How can I modify this query so that only highest level will returned be shown for each user?
Advertisement
Answer
If you want to pull out the entire top row per user, you cannot use aggregation only. Instead, you need some filtering.
If you are running MySQL 8.0, you can use row_number():
select name, level, score
from (
select
user.name,
level.seq as level,
score.score as score,
row_number() over(partition by user.id order by level.seq desc, score.score desc) rn
from api_score score
join api_level level on level.id = score.level_id
join api_user user on user.id = score.user_id
) t
where rn = 1
order by level desc, score desc;