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?
按照SQL:2003扩展名T611中指定的方式，我希望将所有空列最后(空列最后)排序。不幸的是，SQLite似乎并不支持它。有什么好办法吗?
4 个解决方案
#1
17
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
#2
15
While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.
虽然我有点喜欢Blorgbeard的回答，但是这个变体并不关心提供正确数据类型的有效“假”值。
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 ISNULL!
或者，即使您想使用假值，我也希望使用ISNULL!
ORDER BY ISNULL(SOMECOL,-9999)
As Michael noted, SQLite uses IFNULL. You can use the ANSI-SQL universal version COALESCE as well.
正如Michael所指出的，SQLite使用的是IFNULL。您也可以使用ANSI-SQL通用版本合并。
#3
4
You can do something like this to fake it:
你可以做这样的事情来假装它:
select * from test
order by case ordercol when null then 1 else 0 end, ordercol
#4
2
I ran into the same problem. I found out this could work:
我遇到了同样的问题。我发现这是可行的:
(I didn't find any isnull function for SQLite)
(我没有找到任何关于SQLite的isnull函数)
order by ifnull(column_what_you_want_to_sort,'value in case of null')
#1
17
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
#2
15
While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.
虽然我有点喜欢Blorgbeard的回答，但是这个变体并不关心提供正确数据类型的有效“假”值。
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 ISNULL!
或者，即使您想使用假值，我也希望使用ISNULL!
ORDER BY ISNULL(SOMECOL,-9999)
As Michael noted, SQLite uses IFNULL. You can use the ANSI-SQL universal version COALESCE as well.
正如Michael所指出的，SQLite使用的是IFNULL。您也可以使用ANSI-SQL通用版本合并。
#3
4
You can do something like this to fake it:
你可以做这样的事情来假装它:
select * from test
order by case ordercol when null then 1 else 0 end, ordercol
#4
2
I ran into the same problem. I found out this could work:
我遇到了同样的问题。我发现这是可行的:
(I didn't find any isnull function for SQLite)
(我没有找到任何关于SQLite的isnull函数)
order by ifnull(column_what_you_want_to_sort,'value in case of null')