how do I rank students in a given class by highest mark attained?
hi there, I have a query that works perfectly well if executed for just one combined group, but problems begin when I have four classes (W, X, Y, Z) and am trying to rank students in each class. Instead of starting with position 1 its starting with position 4, followed by 8, then 12, etc. When I display for the next class I get ranking with decimal places. Is there any alternative querry I could use to solve this problem or is there anything I'm missing on he particular code below?
SELECT v1.*, COUNT(v2.averagef1) AS Rank
FROM f_teachers, summary1 v1
LEFT JOIN summary1 v2 ON v1.averagef1 < v2.averagef1 OR (v1.averagef1=v2.averagef1 and v1.ssurname = v2.ssurname)
WHERE f_teachers.year = v1.yr AND f_teachers.class= v1.clas AND f_teachers.class= v2.clas AND t_id=%s
GROUP BY v1.ssurname, v1.averagef1
ORDER BY v1.ssurname ASC;