CODEWITHSUNDEEP
phpmysqlsqldatabaserank
Neliswa Astute
Neliswa Astute

Reputation: 71

List correctly the position of student marks in database

I have a query that positions students according to their averages, this mysql query works well when querying positions for the whole class. But when for example I want a position for a specific student, the query ranks that student position one even when in the list the student in not position on.

Below is the query, when querying for the whole class. Here it works well.

    SELECT (@rownum := @rownum + 1) AS rank, student_id, 
   student_name,term_1_avg
   FROM `students` a CROSS JOIN
   (SELECT @rownum := 0) params
   WHERE class = 2 
   ORDER BY term_1_avg DESC

But when the query is like this

    SELECT (@rownum := @rownum + 1) AS rank, student_id, 
     student_name,term_1_avg
   FROM `students` a CROSS JOIN
   (SELECT @rownum := 0) params
   WHERE class = 2 and student_id=2013494
   ORDER BY term_1_avg DESC

it will rank the student number 1 even if that student is not position one.

How can I alter the query such that when I query a student, it shows the position he/she is in the whole class.

Upvotes: 1

Views: 740

Answers (2)

β.εηοιτ.βε
β.εηοιτ.βε

Reputation: 39119

Your ranking is done by the query, so you should keep the query as it and use it as a sub query

SELECT * FROM
(SELECT
    (@rownum := @rownum + 1) AS rank, 
    student_id, 
    student_name,
    term_1_avg
FROM `students` a 
CROSS JOIN (SELECT @rownum := 0) params
WHERE class = 2 
ORDER BY term_1_avg DESC) as sub
WHERE sub.student_id=2013494

Upvotes: 1

Degan
Degan

Reputation: 989

Perhaps this:

Select *
from (SELECT (@rownum := @rownum + 1) AS rank, student_id, 
      student_name,term_1_avg
      FROM `students` a CROSS JOIN
      (SELECT @rownum := 0) params
      WHERE class = 2 
      ORDER BY term_1_avg DESC)
where student_id=2013494

Upvotes: 0

Related Questions