SQL Server ORDER BY date and nulls last

Question

I am trying to order by date  I want the most recent dates coming in first  That s easy enough  but there are many records that are null and those come before any records that have a date   I have tried a few things with no success   ORDER BY ISNULL Next Contact Date  0   ORDER BY ISNULL Next Contact Date  999999999   ORDER BY coalesce Next Contact Date  99 99 9999    How can I order by date and have the nulls come in last  The data type is smalldatetime

User · Answer

A bit late  but maybe someone finds it useful    For me  ISNULL was out of question due to the table scan  UNION ALL would need me to repeat a complex query  and due to me selecting only the TOP X it would not have been very efficient   If you are able to change the table design  you can    Add another field  just for sorting  such as Next Contact Date Sort  Create a trigger that fills that field with a large  or small  value  depending on what you need   CREATE TRIGGER FILL SORTABLE DATE ON YOUR TABLE AFTER INSERT UPDATE AS  BEGIN     SET NOCOUNT ON      IF  update Next Contact Date   BEGIN     UPDATE YOUR TABLE SET Next Contact Date Sort IIF YOUR TABLE Next Contact Date IS NULL  99 99 9999  YOUR TABLE Next Contact Date Sort  FROM inserted i WHERE YOUR TABLE key1 i key1 AND YOUR TABLE key2 i key2     END END

User · Answer

I know this is old but this is what worked for me  Order by Isnull Date  12 31 9999

User · Answer

smalldatetime has range up to  June 6  2079 so you can use  ORDER BY ISNULL Next Contact Date   2079-06-05T23 59 00     If no legitimate records will have that date   If this is not an assumption you fancy relying on a more robust option is sorting on two columns    ORDER BY CASE WHEN Next Contact Date IS NULL THEN 1 ELSE 0 END  Next Contact Date   Both of the above suggestions are not able to use an index to avoid a sort however and give similar looking plans      One other possibility if such an index exists is  SELECT 1 AS Grp  Next Contact Date  FROM T  WHERE Next Contact Date IS NOT NULL UNION ALL SELECT 2 AS Grp  Next Contact Date  FROM T  WHERE Next Contact Date IS NULL ORDER BY Grp  Next Contact Date

User · Answer

Use desc and multiply by -1 if necessary  Example for ascending int ordering with nulls last   select    from  select null v union all select 1 v union all select 2 v  t order by -t v desc

User · Answer

order by -cast  Next Contact Date  as bigint  desc

User · Answer

If your SQL doesn t support NULLS FIRST or NULLS LAST  the simplest way to do this is to use the value IS NULL expression   ORDER BY Next Contact Date IS NULL  Next Contact Date   to put the nulls at the end  NULLS LAST  or   ORDER BY Next Contact Date IS NOT NULL  Next Contact Date   to put the nulls at the front  This doesn t require knowing the type of the column and is easier to read than the CASE expression   EDIT  Alas  while this works in other SQL implementations like PostgreSQL and MySQL  it doesn t work in MS SQL Server  I didn t have a SQL Server to test against and relied on Microsoft s documentation and testing with other SQL implementations  According to Microsoft  value IS NULL is an expression that should be usable just like any other expression  And ORDER BY is supposed to take expressions just like any other statement that takes an expression  But it doesn t actually work   The best solution for SQL Server therefore appears to be the CASE expression

User · Answer

According to Itzik Ben-Gan  author of T-SQL Fundamentals for MS SQL Server 2012   By default  SQL Server sorts NULL marks before non-NULL values  To get NULL marks to sort last  you can use a CASE expression that returns 1 when the  Next Contact Date column is NULL   and 0 when it is not NULL  Non-NULL marks get 0 back from the expression  therefore  they sort before NULL marks  which get 1   This CASE expression is used as the first sort column   The Next Contact Date column  should be specified as the second sort column  This way  non-NULL marks sort correctly among themselves   Here is the solution query for your example for MS SQL Server 2012  and SQL Server 2014     ORDER BY     CASE          WHEN Next Contact Date IS NULL THEN 1         ELSE 0    END  Next Contact Date    Equivalent code using IIF syntax   ORDER BY     IIF Next Contact Date IS NULL  1  0      Next Contact Date

[sql-server] SQL Server ORDER BY date and nulls last

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql