How to fetch rows with prefered where clause, but also with a fallback?

Question

I have a query (transaction) like this for fetching items from a table. I have a where clause of confirmed = '1', but would like to fallback to ignoring that where clause when no rows are found with the clause.

How can I do this in MySQL Server?

START TRANSACTION;

SELECT @id := `id`,`item` 
FROM `queue_items` 
WHERE `processed_at` IS NULL AND `completed_at` IS NULL AND confirmed = '1' ORDER BY `id` ASC 
LIMIT 1
FOR UPDATE;

UPDATE `queue_items` SET `processed_at` = @processedAt, `worker_id` = @workerId WHERE `id` = @id;

COMMIT;

Answer 1

You could use a conditional sort:

SELECT @id := `id`, `item` 
FROM `queue_items` 
WHERE `processed_at` IS NULL AND `completed_at` IS NULL
ORDER BY (confirmed = 1) DESC, `id`
LIMIT 1