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
