CODEWITHSUNDEEP
mysql
Mr.Kinn
Mr.Kinn

Reputation: 409

Return NULL values as well as non-NULL in MySQL query

Below is an excerpt from a table column:

DateCol
…
2009-01-03
2009-01-08
NULL
NULL
2010-01-01
NULL
2010-01-07
NULL
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
…

When I query the table and ask to return 4 values less than 2012-01-01, I get the following result:

2009-01-03
2009-01-08
2010-01-01
2010-01-07

My current query is along the lines of:

SELECT * FROM myDatabase.MyTable WHERE DateCol < "2012-01-01" ORDER BY DateCol LIMIT 4

How can I force the query to return the NULL values as well? I'm looking for a statement that would produce the output below (Note that the order is the same as it appears in the table). There is a primary key column that dictates the order. Is there a way to return the values and NULLs and "preserve" that order?

2010-01-01
NULL
2010-01-07
NULL

Upvotes: 0

Views: 136

Answers (1)

John Conde
John Conde

Reputation: 219814

Use IS NULL to also get the NULL values

SELECT * 
FROM myDatabase.MyTable 
WHERE DateCol < "2012-01-01" OR DateCol IS NULL 
ORDER BY (CASE WHEN DateCol IS NULL then 1 ELSE 0 END)
LIMIT 4

Upvotes: 6

Related Questions