CODEWITHSUNDEEP
mysql
VinoCoder
VinoCoder

Reputation: 1153

Error in mysql query for getting rank from total mark

I want to get rank based on total mark in a student table.

SET @PreviousRecord = NULL;
SET @Rank = 0;
SELECT studentid,total,result CASE
    WHEN @PreviousRecord = total 
        THEN @Rank
    WHEN @PreviousRecord := total 
        THEN @Rank := @Rank + 1
    END AS ranks
FROM studentdetails

ORDER BY total;

Its not working got error as

20 errors were found during analysis. Unrecognized keyword. (near "CASE" at position 30) Unrecognized keyword. (near "WHEN" at position 37) Unexpected token. (near "@PreviousRecord" at position 42) Unexpected token. (near "=" at position 58) Unexpected token. (near "total" at position 60) Unrecognized keyword. (near "THEN" at position 80) Unexpected token. (near "@Rank" at position 85) Unrecognized keyword. (near "WHEN" at position 93) Unexpected token. (near "@PreviousRecord" at position 98) Unexpected token. (near ":=" at position 114) Unexpected token. (near "total" at position 117) Unrecognized keyword. (near "THEN" at position 137) Unexpected token. (near "@Rank" at position 142) Unexpected token. (near ":=" at position 148) Unexpected token. (near "@Rank" at position 151) Unexpected token. (near "+" at po

My table structure is

enter image description here

Upvotes: 0

Views: 336

Answers (1)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

SET @Rank = 0;
SELECT @Rank := @Rank+1,Z.studentid,Z.total
FROM
(
SELECT studentid,total
FROM studentdetails order by total
)Z

Try above query.

Upvotes: 2

Related Questions