MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t1
(
id int(11) unsigned NOT NULL,
val int(11) unsigned NOT NULL,
rank mediumint(7) unsigned,
INDEX(id),
INDEX(rank),
PRIMARY KEY(id,val)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
INSERT INTO t1 (id,val,rank) VALUES (1,2,2), (1,1,1), (1,9,1), (1,5,23), (1,3,2), (2,2,1), (2,9,8), (2,5,0);
SELECT id,GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',') FROM t1 GROUP BY id;


id
GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',')


1
5|23,3|2,2|2,9|1,1|1

2
9|8,2|1,5|0