Menu
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.
522
If you sort a column with NULL values in ascending order, the NULLs will come first. Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last.
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.
The answer is - SQL Server treats NULL values as the lowest values. For example when sorted in ascending order, NULLs come first.
1 Answer. The correct answer is, option (a): Null values are displayed last in the ascending sequences.
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 
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
