This will apparently put null values for myDate at the bottom of the result set. What's the logic behind how this is being executed?
SELECT * FROM myTable
WHERE ...
ORDER BY CASE WHEN myDate IS NULL THEN 1 ELSE 0 END, myDate;
Add a comment
|
2 Answers
This is your order by:
ORDER BY (CASE WHEN myDate IS NULL THEN 1 ELSE 0 END),
myDate
The first expression for the order by says "Give the NULL values a value of 1 (for the sort) and non-NULL values a value of 0". Well, you are sorting in ascending order, so the NULL values go last.
If you want them first, use desc:
ORDER BY (CASE WHEN myDate IS NULL THEN 1 ELSE 0 END) DESC,
myDate
Comments
ORDER BY CASE WHEN myDate IS NULL THEN 1 ELSE 0 END, myDate;
When myDate is null the expression will return 1. Otherwise it will return 0. 1 is greater than 0, so when ordering by the result of that expression in ascending order (the default), null values are moved to the end.
