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
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