Menu
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?
280
If the null ordering is not specified then the handling of the null values is: - NULLS LAST if the sort is ASC - NULLS FIRST if the sort is DESC - If neither ascending nor descending order is specified, and the null ordering is also not specified, then both defaults are used and thus the order will be ascending with ...
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.
Syntax. Following is the basic syntax of using NULL while creating a table. SQLite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Here, NOT NULL signifies that the column should always accept an explicit value of the given data type.
Based on the SQL standard, PRIMARY KEY should always imply NOT NULL . However, SQLite allows NULL values in the PRIMARY KEY column except that a column is INTEGER PRIMARY KEY column or the table is a WITHOUT ROWID table or the column is defined as a NOT NULL column.
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 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.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
