Menu
I have a stored procedure which executes a select statement. I would like my results ordered by a date field and display all records with NULL dates first and then the most recent dates.
The statement looks like this:
SELECT a,b,c,[Submission Date] FROM someView ORDER BY [Submission Date] ASC Now this will display all records with NULL Submission Dates first, but when I get to rows that have date values in them, they are not the most recent dates in the view.
If I replace ASC with DESC, then I get the dates in the the order I want, but the NULL values are at the bottom of my result set.
Is there any way to structure my query so that I can display the null values at the top and then when there are date values, to order them descending most recent to oldest?
323
SQL treats NULL values to be less than 0 so while sorting in ascending order, NULL values always appear to be at first.
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.
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.
@Chris, you almost have it.
ORDER BY (CASE WHEN [Submission Date] IS NULL THEN 1 ELSE 0 END) DESC, [Submission Date] DESC [Edit: #Eppz asked me to tweak the code above as currently shown]
I personally prefer this a lot better than creating "magic numbers". Magic numbers are almost always a problem waiting to happen.
114
You can do something like this put the NULL's at the bottom:
ORDER BY [Submission Date] IS NULL DESC, [Submission Date] ASC 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
