0

I've got a table with 5 columns (student_id, class, sub_class, session, term, percentage). I want each student to be able to view their position according to their sub_class. i.e say there are 30 students in SSII sub_class A. It should show the position of each student with respect to their percentage score, if there happens to be a tie(i.e more than 1 students having the same scores), it should assign the same position to the affected students.

So far, the solution I've been able to get does not consider ties. I need to put into consideration the session, class and sub_class columns as the positions are based strictly on sub-classes. Plus I'd love to have an idea how to insert the SQL query into a PHP script.

halfer
  • 19,824
  • 17
  • 99
  • 186
Shina Adex Adetoye
  • 23
  • 1
  • 7
  • The second answer in the duplicate question shows how to give the same rank when there are ties. – Barmar Feb 13 '15 at 00:26
  • I need to put into consideration the session, class and sub_class columns as the positions are based strictly on sub-classes. Plus I'd love to have an idea how to insert the SQL query into a PHP script.. – Shina Adex Adetoye Feb 13 '15 at 00:34
  • There are lots of other questions that show how to do ranking within groups. Do some searching. And you put it into a PHP script the same way you put any other MySQL query into a script, using the PDO or mysqli libraries. – Barmar Feb 13 '15 at 00:40
  • Since a student's position is dependent on a calculation of all student's positions, I would calculate them all in a loop, and save the position of each student in a new table. You can rewrite this table as often as the situation changes. This will have the effect of caching this query too. – halfer Feb 13 '15 at 00:51
  • I've been able to calculate them all in a loop. The question is, how do I get their position after the calculation? – Shina Adex Adetoye Feb 13 '15 at 00:57
  • Okay, so I used this, SELECT count(*) AS ranking FROM students_positions WHERE percent > (SELECT percent FROM students_positions WHERE student_id ='CSMT/SSS/11/713' ORDER BY percent DESC LIMIT 1 ....but the problem now is that it's not filtering the position in reference to the WHERE parameters I used... – Shina Adex Adetoye Feb 13 '15 at 09:58

0 Answers0