MySQL Query to get the first matched rows in the given list

user_960641 0
Viewed 56

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?

mysql sql
3 Answers

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.

user_18632290
user_101387340

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;

user_96070540
Related