1

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
0

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

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

  • 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. – Julio Aug 3 '16 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.