Use Standard Competition Ranking for your leaderboards
Standard Competition Ranking (SCR) is the system whereby people on a leaderboard are given positions, while taking the possibility of ties into account.
For example, a naïve leaderboard won't know what to do when more than 1 user has the same amount of points. They're all in the same position, yet won't be ranked as such.
SCR makes it so everyone who is in the same position, appears in the same position. It also goes a step further and pushes the person behind them in the rankings the respective number of places backwards.
Eg. instead of having 1, 2, 3, 4, 5, you might have 1, 2, 3, 3, 5, or 1, 1, 1, 1, 5.
That's basically it.
So, because this isn't the easiest or most trivial problem to solve, have a basic class to do it for you. It's nowt special, but it works (and has some test coverage):
https://gist.github.com/3859792
PS. This may or may not balk with anything approaching a large dataset, so you'll want to apply a liberal dose of caching to it (or even optimisation) when you're doing something a bit more strenuous than top 10s or whatever.
Written by Lee Machin
Related protips
3 Responses
IMO, this is usually best done at the database level if possible. For instance - PostgreSQL and most other RDBMSs have rank functions:
select name, score from players order by score desc
name | score
----------+---------
Jacob | 10000
Sophia | 9900
Mason | 9890
Isabella | 9890
William | 9450
Emma | 8725
Jayden | 6520
Olivia | 4960
Noah | 10
Ava | 5
select name, score, rank() as rank from players order by score desc
name | score | rank
----------+---------+------
Jacob | 10000 | 1
Sophia | 9900 | 2
Mason | 9890 | 3
Isabella | 9890 | 3
William | 9450 | 5
Emma | 8725 | 6
Jayden | 6520 | 7
Olivia | 4960 | 8
Noah | 10 | 9
Ava | 5 | 10 Doing it there allows you to offload the processing to the DB, and even gives you the opportunity to use window functions to split the ranking into groups if you want.
Ah, this is good to know! Shame MySQL doesn't have it.
I had no idea that MySQL didn't have a rank() funciton. That's crazy, as it's something that is very widely used.
To be fair, though, it does have powerful LIMIT syntax that allows for easy paging, which is something that requires a couple of levels of nesting to do in SQL Server or Oracle.
I think I'll be sticking with PostgreSQL for my apps, though, to be honest.
