[RESOLVED] Getting user rank determined by their points

OniLink

I'm using the following SQL to determine the top 20 users:

$sql = 'SELECT * FROM users ORDER BY points DESC, username ASC LIMIT 20';

but how would I determine an indiduals rank outside of the top 20. I'm guessing it has something to do with the row, but how do I retrieve that value? Would it be something lile: SELECT ROW?

Thansk for any help,
~Oni.

tomhath

You can to see how many rank higher by using the "COUNT" row aggregate function and selecting users with a higher rank.

OniLink

Sorry for being a pest, but how would I do that?
How would I order the whole user list by their points, then select a particular username from that list, then determine that users rank?

tomhath

I don't know all the column names in your table, but something like this will give you the list. You can add a WHERE clause to select the ID or name that you want:

SELECT u.id, u.username, count( u1.id ) + 1 AS rank
FROM users u
LEFT  JOIN users u1 ON u.points < u1.points
GROUP  BY u.id, u.username
ORDER BY rank

Sxooter

If you know the points of the current person, then you can do:

select count(*) from users where points > $thisuserspoints

should give you the ranking. You can put it in a subquery if you've got them. Otherwise do it in two queries.

OniLink

Thats the one! Thanks Sxooter!