Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select user's high score for each lesson from one table based upon lesson ids in another table

I have a site that has multiple courses and each course has multiple lessons. I would like to select the highest score per LESSON for a given user_id and course_id ordered by lesson_order.

TABLE lesson
id   course_id   lesson_order
---|-----------|--------------
1  |     1     |      1
2  |     1     |      2
3  |     1     |      3
4  |     1     |      4
5  |     1     |      5
6  |     2     |      1
7  |     2     |      2
8  |     2     |      3


TABLE scores
 id   lesson_id   total_score    user_id
----|-----------|--------------|---------
 1  |     1     |      50      |    1
 2  |     1     |      80      |    1
 3  |     1     |      70      |    1
 4  |     2     |      30      |    1
 5  |     2     |      50      |    1
 6  |     3     |      80      |    1
 7  |     3     |      60      |    1
 8  |     3     |      90      |    1
 9  |     3     |      95      |    1
10  |     4     |      50      |    1
11  |     4     |      60      |    1
12  |     4     |      80      |    1
13  |     1     |      90      |    2
14  |     2     |      80      |    2
15  |     2     |      90      |    2
16  |     2     |     100      |    2

I’ve tried the following SQL and many iterations with no luck. I can get the max score for a user, but not for each lesson.

SELECT MAX(s.total_score), s.level_id
FROM (l.id AS score_id, s.total_score, s.level_id, l.lesson_order FROM scores GROUP BY s.level_id) S
JOIN levels L on l.level_id = s.level_id
WHERE user_id = 1 AND course_id = 1
ORDER BY l.lesson_order
Tags:

2

Answers


  1. 0

    On MySQL 8+, we can use ROW_NUMBER() here:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, lesson_id
                                 ORDER BY total_score DESC) rn
    FROM scores
)

SELECT id, lesson_id, total_score, user_id
FROM cte
WHERE rn = 1
ORDER BY user_id, lesson_id;
    Login or Signup to reply.
  2. 0

    With a reqular query:

    SELECT 
    l.id AS lesson_id,
    l.course_id,
    l.lesson_order,
    s.user_id,
    MAX(s.total_score) AS max_score
FROM 
    lesson l
JOIN 
    scores s ON l.id = s.lesson_id
WHERE 
    s.user_id = 1
    AND l.course_id = 1
GROUP BY 
    l.id, l.course_id, l.lesson_order, s.user_id
ORDER BY 
    l.lesson_order;

    Result:

    lesson_id|course_id|lesson_order|user_id|max_score|
---------+---------+------------+-------+---------+
        1|        1|           1|      1|       80|
        2|        1|           2|      1|       50|
        3|        1|           3|      1|       95|
        4|        1|           4|      1|       80|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search