I have the following code it works well, except that I cannot filter to get a specific position of a single student. I shows all students yet I just want to be able to filter and get the position of a specific child.
SELECT users.name,users.lastname, assessement_progress_reports.student_average as avg,
(@rank_count := if((@rn := (@rn + 1)) > 0,
if((assessement_progress_reports.student_average < @prev_value),
@rn,
@rank_count), NULL)
) as rank,
@prev_value := assessement_progress_reports.student_average avg
FROM assessement_progress_reports CROSS JOIN
(SELECT @prev_value := NULL, @rank_count := 1, @rn := 0) init INNER JOIN users on users.id=assessement_progress_reports.student_id where assessement_progress_reports.assessement_id=2 and assessement_progress_reports.student_stream=1 ORDER BY avg DESC