ranking in MySQL in a single sql statement

Question

I have a simple table storing id and the score of the student. I would like to select the rank of a student id in a single sql statement. I have found several post here but they involve parameters or temp table which I don't want to mess with them as I'm using hibernate. Can someone introduce a single SQL statement which can do this job?

id    score
--------------
1      45
2      73
3      94
4      53

Answer 1

Say your table name is student, and 3 is the student id you want to find the rank, and the rank is counted from high to low:

select count(id) + 1 as rank from student 
where score > (select score from student where id = 3)