How to do NULLS LAST in SQLite?
could this work?
SELECT ....... ORDER BY COALESCE(col1,col2,col3,etc) IS NULL
I am kind of confused by your wording "all NULL columns last". If you want all NULL values last in a particular column, use this:
SELECT ....... ORDER BY col1 IS NULL
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.
Release History (v3.30.0 - 2019-10-04)
- Add support for the NULLS FIRST and NULLS LAST syntax in ORDER BY clauses.
ORDER BY
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;
db-fiddle.com demo