CODEWITHSUNDEEP
mysqlsqlsql-update
Enoch
Enoch

Reputation: 1

How do I update scores in table without using a ranking function

Table name is: result

ID  Name   score    position
1   John    40        0
2.  Ali     79        0
3   Ben     50        0
4   Joe     79        0

How can I update table result to give me the table below without using rank() as it does not support by server. Pls someone should help me with the MySQL code That breaks ties just as in table below.

ID  Name  score   position
1   John   40        4
2.  Ali    79        1
3   Ben    50        3
4   Joe    79        1

Upvotes: 0

Views: 161

Answers (3)

Popeye
Popeye

Reputation: 35920

You can use correlated sub-query as follows:

update your_table t
  set t.position = (select count(*) + 1 from your_table tt
                     where tt.score > t.score)

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Lots of ways to skin this particular animal. How about...

 DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID SERIAL PRIMARY KEY
,Name   VARCHAR(12) NOT NULL 
,score  INT NOT NULL
);

INSERT INTO my_table VALUES
(1,'John',40),
(2,'Ali',79),
(3,'Ben',50),
(4,'Joe',79);
  
  SELECT id
       , name
       , score
       , FIND_IN_SET(score, scores) rank
    FROM my_table
   CROSS 
    JOIN 
       ( SELECT GROUP_CONCAT(score ORDER BY score DESC) scores
           FROM my_table
       ) scores
       
+----+------+-------+------+
| id | name | score | rank |
+----+------+-------+------+
|  1 | John |    40 |    4 |
|  2 | Ali  |    79 |    1 |
|  3 | Ben  |    50 |    3 |
|  4 | Joe  |    79 |    1 |
+----+------+-------+------+

I've not provided an UPDATE, because you wouldn't normally store derived data.

Upvotes: 0

id'7238
id'7238

Reputation: 2611

In MySQL prior to version 8 try using the multiple table update syntax:

UPDATE scores t
  LEFT JOIN (
    SELECT t1.id, COUNT(*) + 1 AS new_position
    FROM scores t1
    JOIN scores t2 ON t1.score < t2.score 
    GROUP BY t1.id
  ) agg ON t.id = agg.id
SET t.position = COALESCE(agg.new_position, 1)

fiddle

Upvotes: 1

Related Questions