SQL query for sorting leaderboard

Question

What is the most efficient SQL query for leaderboards if we have table:

[UserID], [Points]

I would like to have the result be sorted by Points and with Rank column.

You tagged this with MySQL and SQL Server. Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. — Taryn, Apr 19 '13 at 12:54

score 2 · Accepted Answer · edited May 23 '17 at 12:21

2

In SQL Server 2008, you can use the Ranking functions:

SELECT UserId,
       Points,
       RANK() OVER(ORDER BY Points DESC) AS Rank
FROM LeaderBoards
ORDER BY Points DESC

A MySql rank could be something like this:

SELECT UserId,
       Points,
       @curRank := @curRank + 1 AS Rank
FROM LeaderBoards l, (SELECT @curRank := 0) r
ORDER BY Points DESC

edited May 23 '17 at 12:21

Community

1
1

answered Apr 19 '13 at 12:54

Cyril Gandon

16,830
14
78
122

ORDER BY Points DESC - more likely? :) – Nenad Zivkovic Apr 19 '13 at 12:57
I the performance of query #1 good enough to work with 1kk records? – Andrei Apr 19 '13 at 13:06

score 1 · Answer 2 · answered Apr 19 '13 at 12:56

1

The query should be:

SELECT UserID, Points FROM table ORDER BY Points

Generating a rank column is probably easier in whatever display technology you're using, ie, php, etc. It can be done in most flavors of SQL but the syntax will vary.

answered Apr 19 '13 at 12:56

user2144429

91
5

Thanks, in fact, with Nenad Zivkovic's query I will get Rank column which is absolutely what I need – Andrei Apr 19 '13 at 13:02

score 0 · Answer 3 · answered May 06 '15 at 01:17

Here is a solution that takes advantage of join:

SELECT t1.UserID, t1.Points, COUNT(t2.Points) AS Rank
FROM LeaderBoards t1
JOIN LeaderBoards t2 ON t1.Points < t2.Points OR (t1.Points=t2.Points and t1.UserID = t2.UserID)
GROUP BY t1.UserID, t1.Points
ORDER BY t1.Points DESC, t1.UserID DESC;

I tested this on MySQL. For a more detailed article on this please see: http://www.artfulsoftware.com/infotree/qrytip.php?id=460

SQL query for sorting leaderboard

3 Answers3