How to do NULLS LAST in SQLite?

Question

I'd like to sort my result with all NULL columns last (NULLS LAST), as specified in the SQL:2003 extension T611. Sadly, SQLite seems to not support it. Is there a clever workaround?

Solution 1

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

Solution 2

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.

Solution 3

SQLite 3.30.0+ is supporting NULLS FIRST/LAST clauses.

Release History (v3.30.0 - 2019-10-04)

2. 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

Solution 4

You can do something like this to fake it:

select * from test
order by case ordercol when null then 1 else 0 end, ordercol 

Solution 5

I ran into the same problem. I found out this could work:

(I didn't find any isnull function for SQLite)

order by ifnull(column_what_you_want_to_sort,'value in case of null')

View more solutions

Comments

[dan04]

Don't you mean IS NOT NULL?

[Blorgbeard]

I think it should be IS NULL to get the nulls last :)

[mvds]

Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)

[mvds]

why not order by case when ordercol is null then 0 else 1, ordercol ? saves a weird bug when someone puts values below -100 in ordercol.

[Blorgbeard]

That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!

[Ortwin Gentz]

Your second variant works just fine for me since I don't need to sort the non-NULL values.

[bart]

In Oracle there is a modification of the ORDER BY clause where you can state you want to sort empty values for a column as first, or as last: ORDER BY col1 NULLS FIRST, ORDER BY col1 NULLS LAST. See Oracle DB - ORDER BY clause. Your ORDER BY col1 is (not) null, col1 is a great substitution for that construct in SQLite.

[Michael]

Minor correction I think ... in SQLite, ISNULL should be IFNULL.