How to do NULLS LAST in SQLite?
While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.
ORDER BY CASE WHEN SOMECOL IS NULL THEN 1 ELSE 0 END, SOMECOL
Alternatively, even if you wanted to use a fake value, I would prefer IFNULL
!
ORDER BY IFNULL(SOMECOL,-9999)
As Michael noted, SQLite uses IFNULL
. You can use the ANSI-SQL universal version COALESCE
as well.
SQLite 3.30.0+ is supporting NULLS FIRST/LAST
clauses.
- Add support for the NULLS FIRST and NULLS LAST syntax in ORDER BY clauses.
SQLite considers NULL values to be smaller than any other values for sorting purposes. Hence, NULLs naturally appear at the beginning of an ASC order-by and at the end of a DESC order-by. This can be changed using the "ASC NULLS LAST" or "DESC NULLS FIRST" syntax.
SELECT * FROM t ORDER BY c NULLS LAST;