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
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 rankingGameRank | 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 DESCGameRank | 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 DESCGameRank | global_sales -------: | -----------: 1 | 300 2 | 250 3 | 200 4 | 125
db<>fiddle here