SQLite: Order by number of NULL values

Question

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;

score 1 · Accepted Answer · answered Nov 10 '20 at 10:16

1

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.

answered Nov 10 '20 at 10:16

GMB

Ok, so the answer is that you'd have to type every column in the query? – Radio Controlled Nov 10 '20 at 10:37
1

@RadioControlled: yes, one way or another you need to enumerate the columns. – GMB Nov 10 '20 at 10:38
but this is not a problem – Radio Controlled Nov 10 '20 at 10:50

1 Answers1