0

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
Lusit
  • 21
  • 4
  • What is MySQL version precisely? – Akina Jul 19 '22 at 05:30
  • *I just want to be able to filter and get the position of a specific child.* Use your query as subquery and filter in outer query. – Akina Jul 19 '22 at 05:31
  • The version is MYSQL 8 – Lusit Jul 19 '22 at 05:41
  • On this version use CTE and window function, not UDVs. PS. https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Akina Jul 19 '22 at 05:51
  • [Why the order of evaluation for expressions involving user variables is undefined?](https://stackoverflow.com/a/44751302/3404097) – philipxy Jul 19 '22 at 07:16
  • How is the "position" of a child defined? Number of feet/meters from the blackboard? Or `RANK()`? `DENSE_RANK()`? – Rick James Jul 20 '22 at 04:01

0 Answers0