MYSQL Dense Rank Trigger or Update Statement

Question

I have a MYSQL table that I need to rank. Each group of child_no needs to be ranked by Parent_NO. I have a select statement see below, that successfully does the ranking. Now I'm looking at at taking the results of the select and actually updating the table. I would ideally like to do this with a trigger on insert,else an update statement that I can run manually will suffice.

Any help on formulating either option would be greatly appreciated. See example on sqlfiddle (I've added an auto id, and target rank column in by statements below): http://sqlfiddle.com/#!9/ebe33/18

Table

CREATE TABLE ranktest
(`parent_no` varchar(10), 
 `child_no` varchar(10), 
 `rankfinal` int,
 `id` MEDIUMINT NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY (id)
);
INSERT INTO ranking
(`parent_no`, `child_no`)
VALUES
('1234', '20'),
('1234', '21'),
('1234', '21'),
('9845', '50'),
('9845', '50'),
('9845', '52'),
('9845', '52'),
('9845', '53'),
('9845', '53'),
('9845', '55'),
('5678', '60'),
('2468', '10'),
('2468', '10');

Select Statement to gather rank

set @rank = 0;
set @prev_child = NULL;
set @prev_parent = NULL;
select parent_no, child_no, rank from (
 select parent_no, child_no, CASE
 when @prev_parent != parent_no then @rank := 1 
 when @prev_child = child_no then @rank
 else @rank := @rank + 1
END as rank, @prev_parent:= parent_no, @prev_child:=child_no
from ranktest
order by parent_no, child_no
) as rankcalc

Answer 1

Switch to MariaDB 10.2 and use their "dense rank" windowing function.

https://mariadb.com/kb/en/mariadb/dense_rank/

• Julio: Thanks for the insight, ideally yes i would migrate, but this is the one limitation that I'm running into, I'd hate to migrate if I can get this resolved.