CODEWITHSUNDEEP
sqlitesql-order-bysql-null
robev
robev

Reputation: 1949

SQLite ORDER BY columns with values

I have a table with four columns, where there could be null values. I want to sort the table such that the rows with the least amount of null columns appear first, and the ones with more null values later. I don't really care about the other values, how they're sorted. Just that all the rows with two nulls come before those with three nulls, etc. For example:

Column1 Column2 Column3 Column4
-------------------------------
    234     213    2222     123
   null     113     232      44
    222    null     111       3
   null      23    null      22
    423     992    null    null
   null    null       2    null
   2192    null    null    null
   null    null    null    null

I've tried various approaches, but none seem to work. For example, I simply sorted using all four columns:

ORDER BY Column1, Column2, Column3, Column4

That didn't work, so then I thought maybe if I consider all null values as 0, and all non-null values 1 it would work:

ORDER BY
CASE WHEN Column1 IS null THEN 1 ELSE 0 END, Column1,
CASE when Column2 IS null THEN 1 ELSE 0 END, Column2,
CASE WHEN Column3 IS null THEN 1 ELSE 0 END, Column3,
CASE WHEN Column4 IS null THEN 1 ELSE 0 END, Column4

I then tried to sort based off the sum of each row:

ORDER BY
CASE WHEN Column1 IS null THEN 1 ELSE 0 END,
CASE when Column2 IS null THEN 1 ELSE 0 END,
CASE WHEN Column3 IS null THEN 1 ELSE 0 END,
CASE WHEN Column4 IS null THEN 1 ELSE 0 END,
Column1 + Column2 + Column3 + Column4

But alas, it doesn't sort properly. Maybe I made a mistake in the syntax...or perhaps there's a different solution?

Upvotes: 1

Views: 28

Answers (1)

forpas
forpas

Reputation: 164154

Sort by the number of null coumns in each row:

ORDER BY (Column1 IS NULL) + (Column2 IS NULL) + (Column3 IS NULL) + (Column4 IS NULL)

Each of the expressions ColumnX IS NULL evaluates to 0 or 1 for false or true.
See the demo.
Results:

| Column1 | Column2 | Column3 | Column4 |
| ------- | ------- | ------- | ------- |
| 234     | 213     | 2222    | 123     |
|         | 113     | 232     | 44      |
| 222     |         | 111     | 3       |
|         | 23      |         | 22      |
| 423     | 992     |         |         |
|         |         | 2       |         |
| 2192    |         |         |         |
|         |         |         |         |

Upvotes: 1

Related Questions