CODEWITHSUNDEEP
mysql
concept104
concept104

Reputation: 89

Assign the same rank if multiple users have same count value

i have user_points Table with two columns.

select user_values, userid from user_points, based on count of userid i want to assgin the rank to users.. i have write this query

SELECT count_temp.* ,  @curRank:=(@curRank + 1) AS rank
 FROM (
    SELECT userid, COUNT(*) AS totalcount FROM user_points t GROUP BY t.userid
 ) AS count_temp 
, (SELECT @curRank := 0) r 
ORDER BY totalcount DESC;

gives the result as :

userid | totalcount | rank 

2        6      1
3        2      2
4        2      3
1        1      4

but i want to assgin to rank 2 for userid 3 and 4 because their totalcount are same ..

Upvotes: 0

Views: 1206

Answers (1)

peterm
peterm

Reputation: 92785

To emulate RANK() function, which returns the rank of each row within the partition of a result set, you can do

SELECT userid, totalcount, rank
  FROM
(
  SELECT userid, totalcount, 
         @n := @n + 1, @r := IF(@c = totalcount, @r, @n) rank, @c := totalcount 
    FROM 
  (
    SELECT userid, COUNT(*) AS totalcount 
      FROM user_points t 
     GROUP BY t.userid
     ORDER BY totalcount DESC
  ) t CROSS JOIN 
  (
    SELECT @r := 0, @n := 0, @c := NULL
  ) i
) q;

Output:

| USERID | TOTALCOUNT | RANK |
|--------|------------|------|
|      2 |          6 |    1 |
|      3 |          2 |    2 |
|      4 |          2 |    2 |
|      1 |          1 |    4 |

To emulate DENSE_RANK() function, which returns the rank of rows within the partition of a result set, without any gaps in the ranking, you can do

SELECT userid, totalcount, rank
  FROM
(

  SELECT userid, totalcount, 
         @r := IF(@c = totalcount, @r, @r + 1) rank, @c := totalcount
    FROM 
  (
    SELECT userid, COUNT(*) AS totalcount 
      FROM user_points t 
     GROUP BY t.userid
     ORDER BY totalcount DESC
  ) t CROSS JOIN 
  (
    SELECT @r := 0, @c := NULL
  ) i
) q;

Output:

| USERID | TOTALCOUNT | RANK |
|--------|------------|------|
|      2 |          6 |    1 |
|      3 |          2 |    2 |
|      4 |          2 |    2 |
|      1 |          1 |    3 |

Here is SQLFiddle demo for both queries

Upvotes: 3

Related Questions