Menu
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?
556
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.
1 Answer. The correct answer is, option (a): Null values are displayed last in the ascending sequences.
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.
select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate
(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
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
41
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
