This is my Sample table

| Col 1 | Col 2 |
| ----- |------ |
| Val 1 | Dat 1 |
| Val 3 | Dat 2 |
| Val 1 | Dat 3 |
| Val 4 | Dat 4 |
| Val 1 | Dat 5 |
| Val 5 | Dat 6 |
| Val 5 | Dat 7 |
| Val 1 | Dat 8 |
| Val 6 | Dat 9 |

I want to extract values form this table based on the first matching value in an ordered list.

For example if the list is (Val 10, Val 1, Val 5, Val 6). I want to get all the rows that have Col 1 as Val 10. If no rows matches, then return rows that have Col 1 as Val 1. If nothing is found repeat the process with next value in the list.

So the expected result for the list (Val 10, Val 1, Val 5, Val 6) is:

| Col 1 | Col 2 |
| ----- |------ |
| Val 1 | Dat 1 |
| Val 1 | Dat 3 |
| Val 1 | Dat 5 |
| Val 1 | Dat 8 |

Example 2: The expected result for the list ( Val 6, Val 10, Val 1, Val 5) is:

| Col 1 | Col 2 |
| ----- |------ |
| Val 6 | Dat 9 |

I tried the following

SELECT `Col 2` 
FROM `table1` 
WHERE `Col 1` IN ('Val 10', 'Val 1', 'Val 5', 'Val 6')
ORDER BY FIELD(`Col 1`, 'Val 10', 'Val 1', 'Val 5', 'Val 6')

But I am getting all the rows from the table that have matches in the list. I am resorting to for loop on the code side. Is there a better way to do it?

CodePudding user response：

On MySQL 8 , we can use DENSE_RANK() here along with a CASE expression:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY CASE `Col 1` WHEN 10 THEN 1
                                                       WHEN 1  THEN 2
                                                       WHEN 5  THEN 3
                                                       WHEN 6  THEN 4 END) dr
    FROM table1
)

SELECT `Col 1`, `Col 2`
FROM cte
WHERE dr = 1;

The above logic ranks all Col 1 = 10 records as 1st rank, Col 1 = 1 records as 2nd rank. It then retains only those records having the first rank.

CodePudding user response：

You can use limit for get only 1 record from database.

SELECT Col 2 FROM table1 WHERE Col 1 IN ('Val 10', 'Val 1', 'Val 5', 'Val 6') ORDER BY FIELD(Col 1, 'Val 10', 'Val 1', 'Val 5', 'Val 6') Limit 1;

CodePudding user response：

On 5.x you may use

SELECT t1.*
FROM test t1
NATURAL JOIN ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('Val 10,Val 1,Val 5,Val 6', ',', MIN(NULLIF(FIND_IN_SET(`Col 1`, 'Val 10,Val 1,Val 5,Val 6'), 0))), ',', -1) AS `Col 1`
               FROM test ) t2

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=794ce9ead93f6e47a013b4b5924a22e2