Mysql, Get Users Rank

Get user rank with MySQL

SELECT 
    COUNT(*) AS rank 
FROM users 
WHERE Points>=(SELECT Points FROM users WHERE User_id=4)

Updated with some more useful stuff:

SELECT 
    user_id, 
    points, 
    (SELECT COUNT(*)+1 FROM users WHERE Points>x.points) AS rank_upper, 
    (SELECT COUNT(*) FROM users WHERE Points>=x.points) AS rank_lower 
FROM 
    `users` x 
WHERE x.user_id = 4

which includes the range of ranks user is at. So for example if the scores for first five places are 5 4 3 3 3, the result would be:

id points rank_upper rank_lower
id 5      1          1
id 4      2          2
id 3      3          5
id 3      3          5
id 3      3          5

MySQL to find rank of specific user if multiple users have same values

You can use ROW_NUMBER() window function such as

SELECT q.rnk
  FROM ( SELECT ROW_NUMBER() OVER (ORDER BY `count` DESC, `id`) AS rnk,
                r.*
           FROM `records` AS r
          WHERE `type` = 1 ) AS q
 WHERE q.`sub_id` = 'ali' -- 'faisal'

Demo

most probably you're currently using DENSE_RANK() or RANK() functions(including ORDER BY count DESC) those may yield equal rank values unlike to ROW_NUMBER()

How to get one user's rank from mysql db?

SET @rank_cnt := 0;
SET @prev_score := NULL;
SELECT * FROM (
   SELECT b.id
    , b.name
    , @rank_cnt := IF(@prev_score = b.score,@rank_cnt,@rank_cnt+1) AS rank
    , @prev_score := b.score AS score
   FROM BBR b
   ORDER BY b.score DESC, b.id DESC
) AS subQ
WHERE subQ.name = "sim";

If you are using the same connection, you shouldn't need that bogus "JOIN" to initialize your session variables.

How to get the rank of a specific user with MySql

I will be glad for the day when MySQL 5x is fully supplanted by MySQL8+ and there are for real Window Functions in MySQL. Until that day, though, for MySQL 5x / MariaDB <10.2, you can use something like:

SELECT username
  , progress_percent
  , gold_count
  , @uRank := @uRank + 1 AS theRank
FROM mytable t, ( SELECT @uRank := 0 ) r
ORDER BY progress_percent DESC, gold_count DESC, modify_date DESC

EDIT: Apparently Window Functions weren't added to MariaDB until 10.2. :-S

If you were using MySQL 8+ or MariaDB 10.2+, you could just use a Rank Window Function:

SELECT username
  , progress_percent
  , gold_count
  , ROW_NUMBER() OVER (ORDER BY progress_percent DESC, gold_count DESC, modify_date DESC ) AS theRank
FROM mytable t
ORDER BY theRank

EDIT 2:

For a specific user, just put the above queries into a subquery and SELECT the Rank you want.

SELECT s1.username, s1.progress_percent, s1.gold_count
FROM (
    [above query here]
) s1
WHERE s1.theRank = <theRankYouWant>

or

WHERE s1.username = <theUsernameYouWant>

get users before and after a specific user with its rank

You already gave the answer inside your question.
It would be (Sql Server style)

DECLARE @myRank int
SELECT @myRank = rank() OVER (Order BY points DESC) FROM client WHERE id = x;

Select *, rank() OVER (Order BY points DESC) as rank
FROM client 
HAVING rank between (@myRank - 5) and (@myRank +5);

If you want it in pure SQL, you'll have to work a little extra, but it's the same idea (just with sub-queries).

MySQL, Get users rank

SELECT  uo.*, 
        (
        SELECT  COUNT(*)
        FROM    users ui
        WHERE   (ui.points, ui.id) >= (uo.points, uo.id)
        ) AS rank
FROM    users uo
WHERE   id = @id

Dense rank:

SELECT  uo.*, 
        (
        SELECT  COUNT(DISTINCT ui.points)
        FROM    users ui
        WHERE   ui.points >= uo.points
        ) AS rank
FROM    users uo
WHERE   id = @id

MySQL find user rank for each category

First, you need to get the total points per category. Then you need to enumerate them. In MySQL this is most easily done with variables:

SELECT user_id, category_id, points,
       (@rn := if(@cat = category_id, @rn + 1,
                  if(@cat := category_id, 1, 1)
                 )
       ) as rank
FROM (SELECT u.user_id, u.category_id, SUM(u.points) as points
      FROM users u
      GROUP BY u.user_id, u.category_id
     ) g cross join
     (SELEct @user := -1, @cat := -1, @rn := 0) vars
ORDER BY category_id, points desc;

SELECT user ranking on MySQL by an offset

In MySQL 8.0, just use window functions, as demonstrated by Gordon Linoff.

In earlier versions, you basically need a subquery to do what you want. I would recommend:

SELECT *
FROM (
    SELECT id, xp, @curRank := @curRank + 1 AS rank
    FROM (SELECT * FROM usuarios ORDER BY xp DESC) u
    CROSS JOIN (SELECT @curRank := 0) r
    ORDER BY xp DESC
) t
ORDER BY xp DESC 
LIMIT 10, 10;

The subquery ranks all users first, then you can safely filter in the outer query. Note that the query pre-orders the table by xp in a subquery first: this is safer (user variables are tricky in MySQL).

Actually, you don't even needLIMIT in the outer query; you can use a WHERE clause instead:

SELECT *
FROM (
    SELECT id, xp, @curRank := @curRank + 1 AS rank
    FROM (SELECT * FROM usuarios ORDER BY xp DESC) u
    CROSS JOIN (SELECT @curRank := 0) r
    ORDER BY xp DESC
) t
WHERE rank BETWEEN 11 AND 20
ORDER BY rank

---

Related Topics