Skip to content
Advertisement

SQLite: Order by number of NULL values

In SQLite, is there any way of ordering by the number of NULL values in each row, without having stored this number explicitly in a separate column?

Something like

SELECT rowid FROM
    (SELECT rowid, COUNT_NULLS(column_1,column_2,...,column_n) AS num_nulls FROM rows)
ORDER BY num_nulls;

Advertisement

Answer

You can count the number of null values in the row in the order by clause, like so:

select *
from mytable t
order by (column_1 is null) + (column_2 is null) + ... (column_n is null)

Condition (column_1 is null) evaluates as 1 if the column is null, else 0.

2 People found this is helpful
Advertisement