How do I rank students in a given class by highest mark attained?

Databases
1

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;

2

several question…

is summary1 a table or a view?

what is the purpose of having the f_teachers table in the query?

are you sure that you will never have more than one student across all your classes with the same surname?

are you displaying the results using an application language like php? (because if so, that’s where you should be doing the ranking)

3

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.

yes, I’m displaying the results using php

4

then you should use a simple ORDER BY in the query (no grouping and counting, no self-join) and apply the ranking numbers as you print out the results

much more efficient, not to mention ~way~ simpler

:slight_smile:

5

thanks its working when it comes to arranging them by order, now the problem comes when I want to write a code which displays the student’s position, how do I do this?

6

use php to count the rows until you get to that student

for more details, i suggest you post a new thread in the php forum (don’t forget to show them your new query, too)