Get reports only last trynumber report of user on exercise _id

Question

I hope you are well

I need if someone can help me with this in MYSQL I use Laravel but direct SQL works the same for me :(

I have a report table which has the columns

-id
-exercise_id (example 1 is not a foreign key since the exercises are not known)
-try_number (user attempt number)
-fk_student (foreign key of a student)

I need to get all the reports (but only 1 per student and exercise_id and only the report with the highest try_number)

For example, if I had this data:

ID: 1
Exercise_ID: 1
TryNumber: 1
FK_Student: 1

ID: 2
Exercise_ID: 1
TryNumber: 2
FK_Student: 1

ID: 3
Exercise_ID: 1
TryNumber: 1
FK_Student: 2

ID: 4
Exercise_ID: 1
TryNumber: 2
FK_Student: 2

ID: 5
Exercise_ID: 1
TryNumber: 3
FK_Student: 2

ID: 6
Exercise_ID: 2
TryNumber: 1
FK_Student: 1

ID: 7
Exercise_ID: 2
TryNumber: 2
FK_Student: 1

I would like to get the following:

ID: 2
Exercise_ID: 1
TryNumber: 2
FK_Student: 1

ID: 5
Exercise_ID: 1
TryNumber: 3
FK_Student: 2

ID: 7
Exercise_ID: 2
TryNumber: 2
FK_Student: 1

I tried grouping but I got confused :(

I review this SQL select only rows with max value on a column [duplicate] but I cant understand how add the where and group if is necessary because this (but only 1 per student and exercise_id and only the report with the highest try_number)

I have this SQL

select * from `reports`
where unity_try_number = (select max(`unity_try_number`) from reports)
group by `fk_student`, `unity_exercise_id`

But I dont know how add where actual fk_student and actual exercise_id on subquery (select max(unity_try_number) from reports)

Thank you very much! Greetings!

Answer 1

Use a correlated subquery:

select r.*
from reports r
where r.unity_try_number = (select max(r2.unity_try_number)
                            from reports r2
                            where r2.fk_student = r.fk_student and
                                  r2.unity_exercise_id = r.unity_exercise_id
                           );