Sunday, December 16, 2012

Query to get the rank of student in a class in MYSQL

Based on my requiremnet i need to get the rank of student in million records in SQL database.Initially i just fetch the students records in decending order .Then put it an array in my code and got the result.This method will reduce the application performance.For this i have done complete data filteration in database using below query

SELECT sub_q.* , @rn := @rn + 1 'Rank_id'
FROM
(SELECT * FROM `student` ORDER BY total_marks DESC,DATE DESC) sub_q,(SELECT @rn := 0) r;
In the above query
1.Initialise the variable rn=0
2.The sub query given data of students records in sort order.
3.Here the rn will be incremneted based on the records count(@rn := @rn + 1).

Initial Data:
Existing Data of Database table
Resultant Data:
resultant output

No comments:

Bel