I have two tables in Snowflake - one with color data and one with ranks for each color:

Color table: | Index | color1 | color2 | color3 | color4 | color5 | | -----| ----- | ----- | ----- | ----- |----- | | 1| red | orange |red |blue |violet | | 2| indigo | yellow | blue | yellow |violet |

Rank table: | Color | Rank | | ---- | ---- | | Red | 1 | | Orange | 2 | | Yellow | 3 | | Green | 4 | | Blue | 5 | | Indigo | 6 | | Orange | 7 | | Violet | 8 |

I'd like to use the two tables above to rank each colors in each row and show top 3 colors in the resulting table. So resulting table will have red, red, and orange for index 1 and yellow, yellow, and blue for index 2.

Any help would be appreciated. Thanks!

I have two tables in Snowflake - one with color data and one with ranks for each color:

Color table:

Rank table:

I'd like to use the two tables above to rank each colors in each row and show top 3 colors in the resulting table. So resulting table will have red, red, and orange for index 1 and yellow, yellow, and blue for index 2.

Any help would be appreciated. Thanks!