MySQL - Generated row numbers are not sequential

Question

I am trying to generate row number for each row selected from my database but it seems that the row number follows the sequence of the table before it's arranged (order by).

Actual table https://www.dropbox.com/s/otstzak20yxcgt6/test1.PNG?dl=0

After query https://www.dropbox.com/s/i9jaoy04vq6u2zh/test2.PNG?dl=0

Code

SET @row_num = 0; 
SELECT @row_num := @row_num + 1 as Position, Student.Stud_ID, Student.Stud_Name, Student.Stud_Class, SUM(Grade.Percentage) AS Points 
FROM Student, Student_Subject, Grade 
WHERE Student.Stud_ID = Student_Subject.Stud_ID 
AND Student_Subject.Stud_Subj_ID = Grade.Stud_Subj_ID 
AND Student.Stud_Form = '1' 
AND Grade.Quarter = '1' 
GROUP BY Student.Stud_ID 
ORDER BY Points DESC

Pls help. Looking forward to receiving replies from yall. Thanks!

Answer 1

Try an inner select, so the row number will be generated after the ORDER BY like so:

SET @row_num = 0; 
SELECT @row_num := @row_num + 1 as Position, s.* 
FROM
(
    SELECT
        Student.Stud_ID, Student.Stud_Name, Student.Stud_Class, SUM(Grade.Percentage) AS Points 
    FROM Student, Student_Subject, Grade 
    WHERE Student.Stud_ID = Student_Subject.Stud_ID 
        AND Student_Subject.Stud_Subj_ID = Grade.Stud_Subj_ID 
        AND Student.Stud_Form = '1' 
        AND Grade.Quarter = '1' 
    GROUP BY Student.Stud_ID 
    ORDER BY Points DESC
) AS s;