Query assigns the same rank to all users-CodePudding

I want to get the ranks/positions of students in a class based on their total scores.

However, my query keeps outputting the same rank to every student; giving all of them the 1st position.

Initially, I tried this query which worked very fine but it brings out all the students in a class.

SELECT `student_id`, rank, totscore
FROM (SELECT *, IF(@marks=(@marks:=totscore), @auto, @auto:=@auto 1) AS rank
FROM (SELECT * FROM
      (SELECT `student_id`, SUM(`ft_tot_score`) AS totscore, `class_id`, `section_id`
       FROM ftscores_primary,
       (SELECT @auto:=0, @marks:=0) as init WHERE class_id = 8 and section_id = 2 and session_id = 17
       GROUP BY `student_id`) sub ORDER BY totscore DESC)t) as result

This is the output:

student_id | rank | totscore
402        |  1   |   869
1314       |  2   |   849
1024       |  3   |   842

But I want something like this:

student_id | rank | totscore
1024       |  3   |  842

This is what I did

SELECT `student_id`, rank, totscore
FROM (SELECT *, IF(@marks=(@marks:=totscore), @auto, @auto:=@auto 1) AS rank
FROM (SELECT * FROM
      (SELECT `student_id`, SUM(`ft_tot_score`) AS totscore, `class_id`, `section_id`
       FROM ftscores_primary,
       (SELECT @auto:=0, @marks:=0) as init WHERE class_id = 8 and section_id = 2 and session_id = 17 and student_id = 1024
       GROUP BY `student_id`) sub ORDER BY totscore DESC)t) as result

Here is what I get

student_id |  rank | totscore
1024       |   1   |  842

CodePudding user response：

Modify your query as the following:

SELECT student_id, rank, totscore
FROM
(
  SELECT student_id, totscore, 
       IF(@marks=(@marks:=totscore), @auto, @auto:=@auto 1) AS rank
  FROM
  (
    SELECT student_id, SUM(ft_tot_score) AS totscore
    FROM ftscores_primary, (SELECT @auto:=0, @marks:=0) as init
    WHERE class_id = 8 and section_id = 2 and session_id = 17
    GROUP BY student_id
  ) T
  ORDER BY totscore DESC
) D
WHERE rank= @auto -- @auto holds the last rank value

See a demo.