SQL how to make null values come last when sorting ascending

sql sorting sql-order-by
David Božjak picture David Božjak · Sep 30, 2009 · Viewed 259.1k times · Source

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?

Answer

D'Arcy Rittich picture D'Arcy Rittich · Sep 30, 2009 
select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate

Related questions

SQL Query - Using Order By in UNION

How can one programmatically sort a union query when pulling data from two tables? For example, SELECT table1.field1 FROM table1 ORDER BY table1.field1 UNION SELECT table2.field1 FROM table2 ORDER BY table2.field1 Throws an exception Note: this …

Read More
How to use SQL Order By statement to sort results case insensitive?

I have a SQLite database that I am trying to sort by Alphabetical order. The problem is, SQLite doesn't seem to consider A=a during sorting, thus I get results like this: A B C T a b c g …

Read More
SQL: Sorting By Email Domain Name

What is the shortest and/or efficient SQL statement to sort a table with a column of email address by it's DOMAIN name fragment? That's essentially ignoring whatever is before "@" in the email addresses and case-insensitive. Let's ignore the internationalized …

Read More