CODEWITHSUNDEEP
mysqlsql
string_loginUsername
string_loginUsername

Reputation: 427

Determine row number for entry in a sorted SQL table

Suppose I have a table storing say student information

id | first_name | last_name | dob | score

where score is some non-unique numeric assessment of their performance. (the schema isn't really relevant, I'm trying to go as generic as possible)

I'd like to know, for any given student, what their score-based overall ranking is. ROW_NUMBER() or any equivalent counter method doesn't really work since they're only accounting for returned entries, so if you're only interested in one particular student, it's always going to be 1.

Counting the number of students with scores greater than the current one won't work either since you can have multiple students with the same score. Would sorting additionally by a secondary field, such as dob work, or would it be too slow?

Upvotes: 0

Views: 40

Answers (1)

esqew
esqew

Reputation: 44694

You should be able to JOIN into a subquery which will provide the ranks of each student across the entire population:

SELECT student.*, ranking.rank
FROM student
JOIN (
    SELECT id, RANK() OVER (ORDER BY score DESC) as rank
    FROM student
) ranking ON student.id = ranking.id

I suppose the scale of your data will be a key determinant of whether or not this is a realistic solution for your use case.

Upvotes: 3

Related Questions