[sql] SQL how to make null values come last when sorting ascending

I have a SQL table with a datetime field. The field in question can be null. I have a query and I want the results sorted ascendingly by the datetime field, however I want rows where the datetime field is null at the end of the list, not at the beginning.

Is there a simple way to accomplish that?

You can use the built-in function to check for null or not null, as below. I test it and its working fine.

select MyDate from MyTable order by ISNULL(MyDate,1) DESC, MyDate ASC;

When your order column is numeric (like a rank) you can multiply it by -1 and then order descending. It will keep the order you're expecing but put NULL last.

select *
from table
order by -rank desc

I also just stumbled across this and the following seems to do the trick for me, on MySQL and PostgreSQL:

ORDER BY date IS NULL, date DESC

as found at https://stackoverflow.com/a/7055259/496209

USE NVL function

  select * from MyTable order by NVL(MyDate, to_date('1-1-1','DD-MM-YYYY'))

Here's the alternative of NVL in most famous DBMS

Solution using the "case" is universal, but then do not use the indexes.

order by case when MyDate is null then 1 else 0 end, MyDate

In my case, I needed performance.

 SELECT smoneCol1,someCol2  
 FROM someSch.someTab
 WHERE someCol2 = 2101 and ( someCol1 IS NULL ) 
  UNION   
 SELECT smoneCol1,someCol2
 FROM someSch.someTab
 WHERE someCol2 = 2101 and (  someCol1 IS NOT NULL)  

order by coalesce(date-time-field,large date in future)

If you're using MariaDB, they mention the following in the NULL Values documentation.

Ordering

When you order by a field that may contain NULL values, any NULLs are considered to have the lowest value. So ordering in DESC order will see the NULLs appearing last. To force NULLs to be regarded as highest values, one can add another column which has a higher value when the main field is NULL. Example:

SELECT col1 FROM tab ORDER BY ISNULL(col1), col1;

Descending order, with NULLs first:

SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;

All NULL values are also regarded as equivalent for the purposes of the DISTINCT and GROUP BY clauses.

The above shows two ways to order by NULL values, you can combine these with the ASC and DESC keywords as well. For example the other way to get the NULL values first would be:

SELECT col1 FROM tab ORDER BY ISNULL(col1) DESC, col1;
--                                         ^^^^

Thanks RedFilter for providing excellent solution to the bugging issue of sorting nullable datetime field.

I am using SQL Server database for my project.

Changing the datetime null value to '1' does solves the problem of sorting for datetime datatype column. However if we have column with other than datetime datatype then it fails to handle.

To handle a varchar column sort, I tried using 'ZZZZZZZ' as I knew the column does not have values beginning with 'Z'. It worked as expected.

On the same lines, I used max values +1 for int and other data types to get the sort as expected. This also gave me the results as were required.

However, it would always be ideal to get something easier in the database engine itself that could do something like:

Order by Col1 Asc Nulls Last, Col2 Asc Nulls First 

As mentioned in the answer provided by a_horse_with_no_name.

If your engine allows ORDER BY x IS NULL, x or ORDER BY x NULLS LAST use that. But if it doesn't these might help:

If you're sorting by a numeric type you can do this: (Borrowing the schema from another answer.)

SELECT *          
FROM Employees
ORDER BY ISNULL(DepartmentId*0,1), DepartmentId;

Any non-null number becomes 0, and nulls become 1, which sorts nulls last because 0 < 1.

You can also do this for strings:

SELECT *
FROM Employees
ORDER BY ISNULL(LEFT(LastName,0),'a'), LastName

Any non-null string becomes '', and nulls become 'a', which sorts nulls last because '' < 'a'.

This even works with dates by coercing to a nullable int and using the method for ints above:

SELECT *
FROM Employees
ORDER BY ISNULL(CONVERT(INT, HireDate)*0, 1), HireDate

(Lets pretend the schema has HireDate.)

These methods avoid the issue of having to come up with or manage a "maximum" value of every type or fix queries if the data type (and the maximum) changes (both issues that other ISNULL solutions suffer). Plus they're much shorter than a CASE.

order by -cast([nativeDateModify] as bigint) desc

(A "bit" late, but this hasn't been mentioned at all)

You didn't specify your DBMS.

In standard SQL (and most modern DBMS like Oracle, PostgreSQL, DB2, Firebird, Apache Derby, HSQLDB and H2) you can specify NULLS LAST or NULLS FIRST:

Use NULLS LAST to sort them to the end:

select *
from some_table
order by some_column DESC NULLS LAST

In Oracle, you can use NULLS FIRST or NULLS LAST: specifies that NULL values should be returned before / after non-NULL values:

ORDER BY { column-Name | [ ASC | DESC ] | [ NULLS FIRST | NULLS LAST ] }

For example:

ORDER BY date DESC NULLS LAST

Ref: http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj13658.html

SELECT *          
FROM Employees
ORDER BY ISNULL(DepartmentId, 99999);

See this blog post.