CODEWITHSUNDEEP
sqlsqlitenull
Ortwin Gentz
Ortwin Gentz

Reputation: 54151

How to do NULLS LAST in SQLite?

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?

Upvotes: 51

Views: 18801

Answers (8)

Lukasz Szozda
Lukasz Szozda

Reputation: 175974

SQLite 3.30.0+ is supporting NULLS FIRST/LAST clauses.

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

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

Upvotes: 35

chef
chef

Reputation: 541

Adding this answer in case anyone is looking for the ASC NULLS LAST functionality (especially for TEXT columns) on an SQLite version before 3.30.0 (so it is unavailable).

To place the nulls last, replace them with a value, and then make sure all other values are lower then it:

SELECT * FROM customers ORDER BY
CASE
    WHEN Company IS NULL THEN '2'
    ELSE '1' || Company
END
ASC;

Also, for INTEGER columns, if keeping number comparison is needed (as opposed to switching lexicographic with text) then just change the NULL to some text. in SQL comparison TEXT > INTEGER

SELECT * FROM customers ORDER BY
CASE
    WHEN IntegerColumn IS NULL THEN 'null'
    ELSE IntegerColumn
END
ASC;

Upvotes: 1

0ne_Up
0ne_Up

Reputation: 501

In my case I wanted to sort descending with NULL columns first. I am adding records to a List from a while loop after querying, so I simply checked if the column was NULL and used ArrayList.add(0, Object) to achieve this.

Upvotes: 0

Evan Moran
Evan Moran

Reputation: 4173

My solution was to reverse the range of my priority column and use order by desc (sort in descending order). This puts nulls at the end in one go.

Clearly this only works if you control your priority ordering column, but it's pretty great if you can swing it!

If that's doesn't work, I found a union query worked. Simply union two queries and add the constraint where not null on the first and where null on the second. The second query will be at the end of the first.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

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.

Upvotes: 26

linczy
linczy

Reputation: 170

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')

Upvotes: 4

Blorgbeard
Blorgbeard

Reputation: 103525

You can do something like this to fake it:

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

Upvotes: 6

mvds
mvds

Reputation: 47104

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

Upvotes: 38

Related Questions