MySQL: Want to use a stored procedure to sort table by one column, then use that ordering to set values of another column

user_13456075 0
Viewed 311

Pretty new to SQL, so I apologize if this is obvious.

I have a table of the number of games sold and their corresponding rank in the bestseller list (1, 2, 3, etc.). This table is called ranking(rank: bigint, global_sales: double).

What I want to do is make a stored procedure that I can call whenever the sales update, and then this procedure can be called to update the rankings. Here's what I have so far, and I'm afraid it's probably very incorrect:

delimiter $$
drop procedure if exists updateRank;
create procedure updateRank()
begin
    select *
    from ranking
    order by global_sales desc;
    
    declare r bigint default 1;
    
    loop1: loop
        GameRank = r;
        set r=r+1;
    end loop loop1;
end $$
delimiter ;

From what I could find here and on Google, I couldn't find anything similar, although this is probably a fairly-common query. Any insight would be greatly appreciated.

Edit: I'm using MySQL Workbench version 8.0 CE

for-loop sql-order-by stored-procedures mysql
1 Answers

you can do it in a loop but also you can do it in a query

Of course i don't know your ölayout of your tables, but thos showws you how you would update

CREATE Table ranking (GameRank  INt,global_sales  INT);

INSERT INTO `ranking` VALUES(0,200),(0,300),(0,250),(0,125)

SELECT * FROM ranking

GameRank | global_sales
-------: | -----------:
       0 |          200
       0 |          300
       0 |          250
       0 |          125

MYsql 5.x

SET @ranking = 0

UPDATE ranking r 
INNER JOIN (SELECT @ranking := @ranking + 1 _rank, global_sales FROM ranking ORDER BY global_sales DESC) t  ON  r.global_sales = t.global_sales
  SET GameRank = _rank

SELECT * FROM ranking   OrDER By global_sales DESC

GameRank | global_sales
-------: | -----------:
       1 |          300
       2 |          250
       3 |          200
       4 |          125

MYSQL 8

UPDATE ranking r 
INNER JOIN (SELECT global_sales,RANK() OVER (  ORDER BY global_sales DESC ) my_rank FROM ranking) t  
ON  r.global_sales = t.global_sales
  SET GameRank = my_rank

SELECT * FROM ranking   OrDER By global_sales DESC

GameRank | global_sales
-------: | -----------:
       1 |          300
       2 |          250
       3 |          200
       4 |          125

db<>fiddle here

user_51935360
Related