Finding The top ranked match in stored procedure

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

SELECT DISTINCT, 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)

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

See the demo.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply