mysql – Finding Same Values and Updating Rankings in MySQL

MySQLPHP

thank you for taking the time to look at this. I have a simple table that I am trying to update based on the entries.

table name is TOPUSER and there us a field called tu_total and tu_rank, I would like to look through this data and see if there are tu_total that are the same and if so those records get the same tu_rank and the tu_rank. the ranking starts out at 1 and goes up from there. Also if a users have a 0 value in tu_total then they all would get the next number above the last user's tu_rank who actually has data in their tu_total.

Here is a sample of what it currently looks like:
What it currently looks like

So as you can see Mark and Bill have the same values of tu_total = 3

So it should look like this:
What is should look like

Best Answer

This will display the rank you requested

SET @prev = 0;
SET @rank = 0;
SELECT name,tu_total total,rnk rank
FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
FROM (SELECT name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AA) AA;

If TOPUSER has an id field, here is the update of the rank

SET @prev = 0;
SET @rank = 0;
UPDATE
(SELECT id,name,tu_total total,rnk rank
FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
FROM (SELECT id,name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AAA) AA) A
INNER JOIN TOPUSER B USING (id)
SET B.tu_rank = A.rank;

SAMPLE DATA

mysql> DROP DATABASE IF EXISTS nate;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE nate;
Query OK, 1 row affected (0.00 sec)

mysql> USE nate
Database changed
mysql> CREATE TABLE TOPUSER
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     tu_total INT NOT NULL,
    ->     tu_rank INT DEFAULT 1000,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO topuser (name,tu_total) VALUES
    -> ('Mark',3),('Bill',3),('Randy',1),('Sammy',0),('Max',0),('Jane',0);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TOPUSER;
+----+-------+----------+---------+
| id | name  | tu_total | tu_rank |
+----+-------+----------+---------+
|  1 | Mark  |        3 |    1000 |
|  2 | Bill  |        3 |    1000 |
|  3 | Randy |        1 |    1000 |
|  4 | Sammy |        0 |    1000 |
|  5 | Max   |        0 |    1000 |
|  6 | Jane  |        0 |    1000 |
+----+-------+----------+---------+
6 rows in set (0.00 sec)

mysql>

UPDATE EXECUTED

mysql> SET @prev = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @rank = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE
    -> (SELECT id,name,tu_total total,rnk rank
    -> FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
    -> FROM (SELECT id,name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AAA) AA) A
    -> INNER JOIN TOPUSER B USING (id)
    -> SET B.tu_rank = A.rank;
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> SELECT * FROM TOPUSER;
+----+-------+----------+---------+
| id | name  | tu_total | tu_rank |
+----+-------+----------+---------+
|  1 | Mark  |        3 |       1 |
|  2 | Bill  |        3 |       1 |
|  3 | Randy |        1 |       2 |
|  4 | Sammy |        0 |       3 |
|  5 | Max   |        0 |       3 |
|  6 | Jane  |        0 |       3 |
+----+-------+----------+---------+
6 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Related Question