Leaderboard

IMHO dense_rank() gives an exaggerated picture of performance. If you get 90% and the other 19 students in your class score 100%, your dense_ranking is 2nd, which is pure BS. If 19 people did better than you then you came 20th (which is what rank() would give. I have tried 2 or 3 times to install mysql version 8 with its window functions, but without success, so I had to do this the hard way. I sed two subqueries - one for the subject positions and the other for the class positions and joined them on REG. SELECT subj.reg , subj.subject , subj.total , subj.subject_rank+0 as subject_rank , clas.class_rank+0 as class_rank FROM ( SELECT reg , @prevrank := CASE WHEN subject <> @prevsub THEN 1 ELSE CASE WHEN total = @prevtot THEN @prevrank ELSE @prevrank + 1 END END as subject_rank , @prevsub := subject as subject , @prevtot := total as total FROM ( SELECT reg , subject , total FROM academic_result WHERE class= 'js1' AND term='1T' AND session='200/2001' ORDER BY subject, total DESC LIMIT 9223372036854775808 -- MariaDB bug workaround ) subj_sorted ) subj JOIN ( SELECT reg , @prevcrank := CASE WHEN tot = @prevctot THEN @prevcrank ELSE @prevcrank + 1 END as class_rank , @prevctot := tot as tot FROM ( SELECT reg , sum(total) as tot FROM academic_result WHERE class= 'js1' AND term='1T' AND session='200/2001' GROUP BY reg ORDER BY tot DESC LIMIT 9223372036854775808 ) cl_sorted ) clas USING (reg) JOIN ( SELECT @prevsub:=0, @prevtot:=0, @prevctot:=0, @prevrank := 0, @prevcrank := 0 ) init ORDER BY class_rank"; giving +-----+---------+-------+--------------+------------+ | reg | subject | total | subject_rank | class_rank | +-----+---------+-------+--------------+------------+ | 2 | bus101 | 90 | 1 | 1 | | 2 | chem101 | 85 | 1 | 1 | | 2 | csc101 | 90 | 1 | 1 | | 1 | bus101 | 70 | 2 | 2 | | 1 | chem101 | 75 | 2 | 2 | | 1 | csc101 | 85 | 2 | 2 | +-----+---------+-------+--------------+------------+