SQL how to make null values come last when sorting ascending

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT             FROM Employees ORDER BY ISNULL DepartmentId  99999     See this blog post

User · Answer

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

User · Answer

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

User · Answer

order by -cast  nativeDateModify  as bigint  desc

User · Answer

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  lt  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     lt   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  quot maximum quot  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

User · Answer

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  --

User · Answer

order by coalesce date-time-field large date in future

User · Answer

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

User · Answer

select MyDate from MyTable order by case when MyDate is null then 1 else 0 end  MyDate

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sorting

Examples related to sql-order-by