How to Sort NULL Values Last in Ascending Order in SQL?

Sorting Null Values Last in Ascending Order

In SQL, sorting ascendingly by a datetime field can be straightforward. However, when the field contains null values, they often appear at the beginning of the sorted list. This can be undesirable, especially if you want null values to come last.

Using a Case Expression

To address this issue, you can employ a case expression within the ORDER BY clause. Here's a practical example:

select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate

How It Works

This SQL query leverages a case expression that assigns a value of 1 to rows where MyDate is null and 0 otherwise. By placing this expression as the first sorting criterion, rows with null values will be sorted last ascendingly.

Additional Considerations

• Ascending Order: The provided solution is optimized for ascending order sorting. If you require descending order, you can modify the case expression to assign null values a value of 0 and non-null values a value of 1.
• Other Datatypes: This technique can be applied to other datatypes as well, such as numbers or strings. Simply adjust the comparison and sorting accordingly.

The above is the detailed content of How to Sort NULL Values Last in Ascending Order in SQL?. For more information, please follow other related articles on the PHP Chinese website!