CODEWITHSUNDEEP
mysqlsqlsql-order-by
nieburr
nieburr

Reputation: 13

MySQL: Look for specific value first then get a second value

My code so far

SELECT * FROM gp 
WHERE status IN ('priority', NULL)  
   AND (status='priority' OR EXISTS(SELECT * FROM gp WHERE status IS NULL)) 
LIMIT 1

I'm trying to get the first row with status = 'priority' if one exists, or else get the first row with status = NULL. There may be other values for status as well, but I only want to get priority first if available, then NULL.

What my code does so far is, it returns the rows with status = 'priority' when they exist, but returns nothing when only NULLs exist.

Upvotes: 0

Views: 987

Answers (1)

Nick
Nick

Reputation: 147196

You can't use NULL in an IN expression, so you need to change the first part of your WHERE clause to

WHERE status = 'priority' OR status IS NULL

You can remove the second part of the WHERE clause and simply

ORDER BY status = 'priority` DESC

this will put rows which have status = 'priority' ahead of those which have status of NULL

So your query as a whole becomes:

SELECT * FROM gp 
WHERE status = 'priority' OR status IS NULL
ORDER BY status = 'priority` DESC
LIMIT 1

Note that to get the "first" row with status = 'priority' you need an ordering column (SQL tables are unordered, so without an ordering column "first" has no meaning), and you can add that to the ORDER BY clause:

SELECT * FROM gp 
WHERE status = 'priority' OR status IS NULL
ORDER BY status = 'priority` DESC, id ASC
LIMIT 1

Upvotes: 2

Related Questions