SQL: How to select a single row based on a column matched against multiple possible values with preferred match order?

Using MariaDB.

Say you have a table, one of the columns is color and is the primary key so it’s unique.

I want a select statement to get one row based on color but i want to search for three possible color matches in a preferred order.

A normal WHERE color='red' OR color='blue' OR color='green' won’t work because that would return multiple rows. I can’t use ORDER BY and LIMIT because the three search values are not alphabetical.

I want to search for color='red' and if there is a red give me just that one row. If there is no red then give me the row that color='blue'. If there are no red or blue then give me the row with color='green'. But just to be clear, when searching for the first color red, even if there is a blue and green only return the red because its the preferred match. But if no red then down the line for the next color blue.

I looked at if(), IF...THEN and CASE and didn’t see how to do this. Is there a way?

Answer

You can use an ORDER BY clause and LIMIT 1:

WHERE color='red' OR color='blue' OR color='green'
ORDER BY color='red' DESC, 
         color='blue' DESC,
         color='green' DESC -- not actually needed
LIMIT 1;

MySql and Mariadb evaluate boolean expressions like color='red' as 1 for true and 0 for false, so when you sort by color='red' DESC the row that returns 1 (the row with color='red') will be on top and all the others next.

Or, use a CASE expression:

WHERE color='red' OR color='blue' OR color='green'
ORDER BY CASE color
  WHEN 'red' THEN 1
  WHEN 'blue' THEN 2
  WHEN 'green' THEN 3
END
LIMIT 1;