Question 273053 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql rank different students with multiple subject results in same table

I want to rank the students based on the subject, and their total overall for that particular subject. Query works for all subjects but when I specify the where clause, I only get the rank of the user specified in the where clause and rank is always 1 seems it’s not checking the other ids

enter image description here

SELECT 
    Student_id,
    Subject,
    Total_Overal,
    RANK() OVER (PARTITION BY Subject ORDER BY Total_Overal DESC) as StudentRank
FROM
    exams 
WHERE 
    Student_id = "155676" 
    AND Subject = "French";
Tags:

2

Answers


  1. 0

    Because the where clause results in only one row of results, the ranking will always be 1. You need to add another layer of SELECT around:

    SELECT * FROM
(SELECT Student_id,
    Subject,
    Total_Overal,
    RANK() OVER (PARTITION BY Subject ORDER BY Total_Overal DESC) as StudentRank
FROM exams) t
WHERE Student_id="155676" and Subject="French";
    Login or Signup to reply.
  2. 0

    If you understand SQL order of execution, you will understand why you get what you get. WHERE is evaluated before SELECT.

    FROM and JOIN.
WHERE.
GROUP BY.
ROLLUP , CUBE , GROUPING SETS.
HAVING.
OVER (e.g., Window Functions)
SELECT.
DISTINCT.

    https://vladmihalcea.com/sql-operation-order/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search