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:
I have another table like below
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|
|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.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂