Output unique rank (row number) based on score and name

Question

I have a score list and want to output the user's rank. The code I use outputs the same rank for users with equal score whereas I need unique rank for each user (ordered by pts and then by userName) like this:

  uid | userName | pts | rank
  -------------------------
  3   | Jimmy    | 100 | 1
  10  | Alex     | 98  | 2 (because of userName)
  8   | John     | 98  | 3
  6   | Peter    | 96  | 4

Here's the code I'm working with:

SELECT 
    COUNT(*) AS rank 
FROM tbl_users 
WHERE pts>=(SELECT pts FROM tbl_users WHERE userID=:uid)

score 1 · Answer 1 · edited May 23 '17 at 12:16

1

Try this query:

SET @rank=0;
SELECT uid,
       userName,
       pts,
       @rank := @rank+1 AS rank
FROM tbl_users
ORDER BY pts DESC,
         userName

Closely related is this SO question, but in your case you need to order by multiple columns to deal with users having the same number of points.

Update:

If the points column is actually computed, then perhaps this query will work:

SET @rank=0;
SELECT uid,
       userName,
       COUNT(*) AS pts,
       @rank := @rank+1 AS rank
FROM tbl_users
GROUP BY uid,
         userName
ORDER BY pts DESC,
         userName

edited May 23 '17 at 12:16

Community

1
1

answered Dec 20 '16 at 17:54

Tim Biegeleisen

502,043
27
286
360

Doesn't seem to work. Maybe this would output a list but I need just the current user's rank. – user1542894 Dec 20 '16 at 18:06
@user1542894 Is the points column a computed column or does it really exist in the `tbl_users` table (I assumed the latter)? – Tim Biegeleisen Dec 20 '16 at 18:11
Yes they exist in the table. Still I get this error: `Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error' in... Stack trace: #0 ...: PDOStatement->fetch(2) #1 {main}`. As you can see I'm using PDO and get the result with `fetch(PDO::FETCH_ASSOC);` – user1542894 Dec 20 '16 at 18:17
@user1542894 Then maybe your PHP code has some problem. – Tim Biegeleisen Dec 20 '16 at 18:19
Seems like I get that error whenever I use variables like `SET @rank=0;`. This is the php and It's working fine with the code I presented: `$stmt = $user_home->runQuery(//sql goes here); $stmt->execute(array(":uid"=>$uid)); $isrank = $stmt->fetch(PDO::FETCH_ASSOC);` Also, shouldn't there be a `WHERE userID=:uid` clause somewhere so that only the current user's rank is showed? – user1542894 Dec 20 '16 at 18:44

David דודו Markovitz · Answer 2 · 2016-12-23T11:17:49.253

(The ORDER BY is just for display purposes)

select      u1.*
          ,(select  count(*) 
            from    tbl_users u2 
            where   u2.pts > u1.pts
                 or (   u2.pts       = u1.pts
                    and u2.userName <= u1.userName 
                    )
            )   as rank

from        tbl_users u1

order by   rank
;

+-----+----------+-----+------+
| uid | userName | pts | rank |
+-----+----------+-----+------+
| 3   | Jimmy    | 100 | 1    |
+-----+----------+-----+------+
| 10  | Alex     | 98  | 2    |
+-----+----------+-----+------+
| 8   | John     | 98  | 3    |
+-----+----------+-----+------+
| 6   | Peter    | 96  | 4    |
+-----+----------+-----+------+

Philipp · Accepted Answer · 2016-12-23T10:19:47.770

Looking for the rank of a user

You only have to count the users who have

a better score
the same score but a "better" name

So you can use those conditions to join on the same table.

SELECT
    main.*,
    count(*) AS rank
FROM
    tbl_users main
LEFT JOIN tbl_users AS comparison 
    ON (comparison.pts > main.pts)
        OR (
            comparison.pts = main.pts
            AND comparison.userName <= main.userName
        )
WHERE
    main.uid = :uid

Example

Ranking all the users

Your question is a little confusing, nobody seems to be sure if you want the rank of a specific user or a list of all users. So here is my solution for the list:

SELECT
    uid,
    userName,
    pts,
    @curRank := @curRank + 1 AS rank
FROM
    tbl_users
CROSS JOIN (SELECT @curRank := 0) AS rank
ORDER BY
    pts DESC,
    userName;

Example

In the first query you are loosing the first record. Change inner join to left join or < to <= — David דודו Markovitz, Dec 23 '16 at 09:54

score 1 · Answer 4 · answered Dec 25 '16 at 03:59

Using the ranking query and PDO

In some of your comments you have mentioned that you are trying to use PDO to execute the query and you need to find the rank of a specific user. To achieve this you need to sorround the standard ranking query (pretty much what Tim has posted in his answer) with an outer query.

And then, we need to recall that PDO doesn't support multiple statements in a single call to execute. So we do it like this.

$dbh = new PDO(...);

$n = 8; # some value that probably comes from GET or POST

$stmt = $dbh->prepare("SET @rank=0");
$stmt->execute();

$stmt = $dbh->prepare("SELECT * FROM (
         SELECT uid,
           userName,
           pts,
           @rank := @rank+1 AS rank
    FROM tbl_users
    ORDER BY pts DESC,
             userName) AS a WHERE uid=?"); 

$stmt->bindParam(1,$n);


$stmt->execute();
while ($a = $stmt->fetch()) {
    print_r($a);
}

Be warned that this query might be a triffle slow if you have a large number of rows in your table.

Other options

There are several other solutions that involve LEFT JOINs with inequality comparisons and CROSS JOINs. These will probably be as slow as or even slower than the current query.

RANK,DENSERANK and ROW_NUMBER functions are only available in MariaDB 10.2 onwards and not available in other flavours of mysql. Unfortunately Mariadb 10.2 isn't recommended for production.

score 1 · Answer 5 · answered Dec 30 '16 at 20:48

I know an answer has already been accepted, but I thought I would offer this up anyways just for fun.

It seems a shame that someone could outrank someone else with the same points just because their name is "better".

The following allows for equal rank for equal points:

set @rank := 0;
select RANK.rank, U.uid, U.userName, U.pts
  from users U
  join ( select P1.pts, min( @rank := @rank + 1 ) as rank
           from ( select U1.pts from users U1 order by pts desc ) P1
          group by P1.pts
       ) RANK
    on RANK.pts = U.pts
 order by RANK.rank, U.userName;

results in:

rank    uid     userName    pts
1       3       Jimmy       100
2       10      Alex        98
2       8       John        98
4       6       Peter       96

See it on SQL Fiddle

score 0 · Answer 6 · answered Dec 23 '16 at 03:24

0

You need RANK() or DENSE_RANK() of MariaDB 10.2. See https://mariadb.com/kb/en/mariadb/rank/

answered Dec 23 '16 at 03:24

Rick James

135,179
13
127
222

score 0 · Answer 7 · answered Dec 23 '16 at 10:38

0

you can try this

SELECT * FROM (SELECT * FROM tbl_users ORDER BY `userName` ASC) AS t GROUP BY `pts` order by `pts` DESC

answered Dec 23 '16 at 10:38

Vaghani Janak

601
5
14

score 0 · Answer 8 · answered Dec 26 '16 at 13:05

0

can you check this :

SET @rank=0;
SELECT uid, userName, pts, @rank := @rank+1 AS rank FROM tbl_users ORDER BY amount DESC;

answered Dec 26 '16 at 13:05

BGH

56
1
6

Output unique rank (row number) based on score and name

8 Answers8

Using the ranking query and PDO

Other options