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