Problem
I have a datetime field in a SQL table. It’s possible that the field in question is empty. I have a query that I want sorted ascendingly by the datetime field, but I only want rows where the datetime field is null towards the end of the list, not at the beginning.
Is there a straightforward way to do so?
Asked by David Božjak
Solution #1
select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate
Answered by D’Arcy Rittich
Solution #2
(It’s a little late, but this hasn’t been stated.)
You didn’t say what database management system you were using.
You can define NULLS LAST or NULLS FIRST in normal SQL (and most current DBMS including Oracle, PostgreSQL, DB2, Firebird, Apache Derby, HSQLDB, and H2):
To sort them to the end, use NULLS LAST:
select *
from some_table
order by some_column DESC NULLS LAST
Answered by a_horse_with_no_name
Solution #3
I recently came into this as well, and the following seems to work for me on MySQL and PostgreSQL:
ORDER BY date IS NULL, date DESC
Answered by Luksurious
Solution #4
Use ORDER BY x IS NULL, x or ORDER BY x NULLS LAST if your engine supports it. However, if it doesn’t, the following may be of assistance:
You can do the following if you’re sorting by a number type: (Adapted from another answer’s schema.)
SELECT *
FROM Employees
ORDER BY ISNULL(DepartmentId*0,1), DepartmentId;
Because 0 1, each non-null number becomes 0 and nulls become 1, nulls are sorted last.
You can accomplish the same thing with strings:
SELECT *
FROM Employees
ORDER BY ISNULL(LEFT(LastName,0),'a'), LastName
Any non-null string is converted to “, while nulls are converted to ‘a’, causing nulls to be sorted last since ” ‘a’.
By coercing to a nullable int and using the method for ints above, this also works with dates:
SELECT *
FROM Employees
ORDER BY ISNULL(CONVERT(INT, HireDate)*0, 1), HireDate
(Let’s presume HireDate is in the schema.)
These methods eliminate the need to create or manage a “maximum” number for each type of data, as well as the need to adjust queries if the data type (and the maximum) changes (both issues that other ISNULL solutions suffer). They’re also a lot shorter than a CASE.
Answered by infogulch
Solution #5
As seen below, you may use the built-in function to check for null or not null. I put it to the test and it works perfectly.
ISNULL(MyDate,1) DESC, MyDate ASC; select MyDate from MyTable order by ISNULL(MyDate,1) DESC, MyDate ASC;
Answered by Majdi M. Aburahelah
Post is based on https://stackoverflow.com/questions/1498648/sql-how-to-make-null-values-come-last-when-sorting-ascending