CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008t-sql
Andrei
Andrei

Reputation: 44600

SQL query for sorting leaderboard

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.

Upvotes: 0

Views: 723

Answers (3)

Todd Palmer
Todd Palmer

Reputation: 1102

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

Upvotes: 0

Cyril Gandon
Cyril Gandon

Reputation: 17058

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

Upvotes: 2

user2144429
user2144429

Reputation: 91

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.

Upvotes: 1

Related Questions