Finding The top ranked match in stored procedure

Question

I have a situation for which I need guidance/solution.

I have a table which contains the Order as below:

color	rank
Red	1
Blue	2
Green	3
Yellow	4
White	5
Black	6

I have another table like below

id	ColorValue
ID1	Red,Yellow
ID2	Green
ID3	White
ID4	Green,Red
ID5	Yellow, White,Black

Now, the first table defines the selected color based on priority and would like colorValue to be replaced with the color which is ranked highest (1 is high and 6 is lowest).

The output I want is as below:

id	ColorValue	Value to be replaced		reason
ID1	Red,Yellow	Red		Red is ranked higher than yellow
ID2	Green	Green
ID3	White	White
ID4	Green,Red	Red		Red is ranked higher than Green
ID5	Yellow, White,Black	Yellow		Yellow is ranked highest among 3 colors

Please let me know how can I achieve this.

Answer 1

You can join the tables and use FIRST_VALUE() window function to get the higher ranked color:

SELECT DISTINCT t2.id, t2.ColorValue, 
       FIRST_VALUE(t1.color) OVER (PARTITION BY t2.ColorValue ORDER BY t1.`rank`) Value_to_be_replaced
FROM table2 t2 INNER JOIN table1 t1
ON FIND_IN_SET(t1.color, t2.ColorValue)
ORDER BY t2.id

This will work in MySql 8.0+.

For prior versions use a correlated subquery:

SELECT t2.*,
       (
         SELECT t1.color
         FROM table1 t1
         WHERE FIND_IN_SET(t1.color, t2.ColorValue)
         ORDER BY t1.`rank` LIMIT 1
       ) Value_to_be_replaced
FROM table2 t2
ORDER BY t2.id

See the demo.