Skip to content
Advertisement

JOIN with 3 tables, and MAX value for a column combination

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;
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement