How can I get the rank of rows relative to total number of rows based on a field?
- by Arms
I have a scores table that has two fields:
user_id
score
I'm fetching specific rows that match a list of user_id's. How can I determine a rank for each row relative to the total number of rows, based on score? The rows in the result set are not necessarily sequential (the scores will vary widely from one row to the next). I'm not sure if this matters, but user_id is a unique field.
Edit
@Greelmo
I'm already ordering the rows. If I fetch 15 rows, I don't want the rank to be 1-15. I need it to be the position of that row compared against the entire table by the score property. So if I have 200 rows, one row's rank may be 3 and another may be 179 (these are arbitrary #'s for example only).
Edit 2
I'm having some luck with this query, but I actually want to avoid ties
SELECT
s.score
, s.created_at
, u.name
, u.location
, u.icon_id
, u.photo
, (SELECT COUNT(*) + 1 FROM scores WHERE score > s.score) AS rank
FROM
scores s
LEFT JOIN
users u ON u.uID = s.user_id
ORDER BY
s.score DESC
, s.created_at DESC
LIMIT 15
If two or more rows have the same score, I want the latest one (or earliest - I don't care) to be ranked higher. I tried modifying the subquery with AND id > s.id but that ended up giving me an unexpected result set and different ties.
