CODEWITHSUNDEEP
databasemysqlranking
Thang Pham
Thang Pham

Reputation: 38705

What is a good design for Ranking System

I have a USER table, that has userId and point fields. At runtime, I want to know what is a ranking of a particular user base on their point. What is the best way to accomplish this:
1: Query all users into a list. Sort the list base on point and do a binary search to find the ranking of that user. Sound like a bad idea here.
2: Is it possible to accomplish these tasks by creating database queries?

I expect 2000-5000 users.

Upvotes: 3

Views: 2635

Answers (1)

Victor Parmar
Victor Parmar

Reputation: 5789

SET @rownum := 0;

SELECT rank, userId, point 
FROM (
       SELECT @rownum := @rownum + 1 AS rank, userId, point
       FROM user ORDER BY point DESC
     ) 
as result WHERE userId = xxxxxxxx

Upvotes: 5

Related Questions