2

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

2
  • Yes, and how have you tried to solve this?
    – mico
    Commented Jun 9, 2015 at 17:38
  • I have looked at each tu_total to see if it matches and if so get the lowest rank and update all with the found match on tu_total. BUT that messes up the overall tu_rank
    – Nate
    Commented Jun 9, 2015 at 17:48

1 Answer 1

2

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 !!!

3
  • that does work as far as displaying what I want to see but if I need to update the rankings in the table with the way its displayed?
    – Nate
    Commented Jun 9, 2015 at 18:10
  • Refresh your page. I just added the update code Commented Jun 9, 2015 at 18:11
  • I referenced this for something very similar but for some reason the SET's caused it to not work (the @rank would just count up). Once I took them away, it worked fine Commented Jun 25, 2021 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.