MYSQL Dense Rank Trigger or Update Statement 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
Switch to MariaDB 10.2 and use their "dense rank" windowing function.

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