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;
summary1 is a table with all the students in different classes,
f_teachers is a table containing form teachers for all the classes
when the form teacher logs in, his class list will be displayed and yes there may be students with same surnames in other classes, but what uniquely identifies and matches each form teacher and his students is the class.