0

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
Mr.Kinn
  • 309
  • 6
  • 18

1 Answers1

6

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
John Conde
  • 217,595
  • 99
  • 455
  • 496
  • I updated the answer to put nulls last (see http://stackoverflow.com/questions/5993109/order-by-asc-with-nulls-at-the-bottom) – John Conde Sep 20 '13 at 00:24
  • Thank you for your response. I don't think I was clear enough in my question :(. I need the query to return the `NULL` and actual values in the order they appear in the database... I have updated my question to clarify. – Mr.Kinn Sep 20 '13 at 00:31
  • "I need the query to return the NULL and actual values in the order they appear in the database" - this is not something that you can ever guarantee unless you have some other column that guarantees this ordering (and, thus, can be referenced in an order by). While a select statement may sometimes return rows in the order that they were inserted (or "appear"), that is not always the case and you can never rely on that being the case. The only way to guarantee ordering is by specifying an order by clause. – Derek Sep 20 '13 at 00:40
  • @DerekKromm: I have a primary key column that keeps track of the "proper" order. Would I just reference that in the `ORDER BY` statement? – Mr.Kinn Sep 20 '13 at 00:46
  • 1
    Yes. `ORDER BY primaryKeyColName ASC` – John Conde Sep 20 '13 at 00:48