How to do NULLS LAST in SQLite?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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 sqlite sqlite3 null
add a comment |
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 sqlite sqlite3 null
add a comment |
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 sqlite sqlite3 null
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 sqlite sqlite3 null
sql sqlite sqlite3 null
edited Sep 19 '12 at 21:27
Mat
168k29323349
168k29323349
asked Sep 19 '12 at 21:23
Ortwin GentzOrtwin Gentz
32.7k20114189
32.7k20114189
add a comment |
add a comment |
5 Answers
5
active
oldest
votes
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
Don't you meanIS NOT NULL
?
– dan04
Sep 19 '12 at 21:28
@dan04 thx, you're right
– mvds
Sep 19 '12 at 21:30
3
I think it should beIS NULL
to get the nulls last :)
– Blorgbeard
Sep 19 '12 at 21:30
@Blorgbeard crap...
– mvds
Sep 19 '12 at 21:31
Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)
– mvds
Sep 19 '12 at 21:32
|
show 2 more comments
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.
Minor correction I think ... in SQLite, ISNULL should be IFNULL.
– Michael
Jan 1 '17 at 20:03
Thanks @Michael, updated with better info
– RichardTheKiwi
Jan 3 '17 at 2:49
add a comment |
You can do something like this to fake it:
select * from test
order by case ordercol when null then 1 else 0 end, ordercol
why notorder by case when ordercol is null then 0 else 1, ordercol
? saves a weird bug when someone puts values below -100 in ordercol.
– mvds
Sep 19 '12 at 21:36
That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!
– Blorgbeard
Sep 19 '12 at 21:38
add a comment |
I ran into the same problem.
I found out this could work:
(I didn't find any isnull
function for SQLite)
order by ifnull(column_what_you_want_to_sort,'value in case of null')
add a comment |
My solution was to reverse the range of my priority
column and use order by desc
(sort in descending order). This puts nulls at the end in one go.
Clearly this only works if you control your priority
ordering column, but it's pretty great if you can swing it!
If that's doesn't work, I found a union query worked. Simply union
two queries and add the constraint where not null
on the first and where null
on the second. The second query will be at the end of the first.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f12503120%2fhow-to-do-nulls-last-in-sqlite%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
Don't you meanIS NOT NULL
?
– dan04
Sep 19 '12 at 21:28
@dan04 thx, you're right
– mvds
Sep 19 '12 at 21:30
3
I think it should beIS NULL
to get the nulls last :)
– Blorgbeard
Sep 19 '12 at 21:30
@Blorgbeard crap...
– mvds
Sep 19 '12 at 21:31
Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)
– mvds
Sep 19 '12 at 21:32
|
show 2 more comments
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
Don't you meanIS NOT NULL
?
– dan04
Sep 19 '12 at 21:28
@dan04 thx, you're right
– mvds
Sep 19 '12 at 21:30
3
I think it should beIS NULL
to get the nulls last :)
– Blorgbeard
Sep 19 '12 at 21:30
@Blorgbeard crap...
– mvds
Sep 19 '12 at 21:31
Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)
– mvds
Sep 19 '12 at 21:32
|
show 2 more comments
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
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
edited Sep 19 '12 at 21:34
answered Sep 19 '12 at 21:26
mvdsmvds
39.3k686103
39.3k686103
Don't you meanIS NOT NULL
?
– dan04
Sep 19 '12 at 21:28
@dan04 thx, you're right
– mvds
Sep 19 '12 at 21:30
3
I think it should beIS NULL
to get the nulls last :)
– Blorgbeard
Sep 19 '12 at 21:30
@Blorgbeard crap...
– mvds
Sep 19 '12 at 21:31
Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)
– mvds
Sep 19 '12 at 21:32
|
show 2 more comments
Don't you meanIS NOT NULL
?
– dan04
Sep 19 '12 at 21:28
@dan04 thx, you're right
– mvds
Sep 19 '12 at 21:30
3
I think it should beIS NULL
to get the nulls last :)
– Blorgbeard
Sep 19 '12 at 21:30
@Blorgbeard crap...
– mvds
Sep 19 '12 at 21:31
Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)
– mvds
Sep 19 '12 at 21:32
Don't you mean
IS NOT NULL
?– dan04
Sep 19 '12 at 21:28
Don't you mean
IS NOT NULL
?– dan04
Sep 19 '12 at 21:28
@dan04 thx, you're right
– mvds
Sep 19 '12 at 21:30
@dan04 thx, you're right
– mvds
Sep 19 '12 at 21:30
3
3
I think it should be
IS NULL
to get the nulls last :)– Blorgbeard
Sep 19 '12 at 21:30
I think it should be
IS NULL
to get the nulls last :)– Blorgbeard
Sep 19 '12 at 21:30
@Blorgbeard crap...
– mvds
Sep 19 '12 at 21:31
@Blorgbeard crap...
– mvds
Sep 19 '12 at 21:31
Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)
– mvds
Sep 19 '12 at 21:32
Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)
– mvds
Sep 19 '12 at 21:32
|
show 2 more comments
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.
Minor correction I think ... in SQLite, ISNULL should be IFNULL.
– Michael
Jan 1 '17 at 20:03
Thanks @Michael, updated with better info
– RichardTheKiwi
Jan 3 '17 at 2:49
add a comment |
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.
Minor correction I think ... in SQLite, ISNULL should be IFNULL.
– Michael
Jan 1 '17 at 20:03
Thanks @Michael, updated with better info
– RichardTheKiwi
Jan 3 '17 at 2:49
add a comment |
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.
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.
edited Jan 4 at 12:12
TWiStErRob
28.2k8106190
28.2k8106190
answered Sep 19 '12 at 21:37
RichardTheKiwiRichardTheKiwi
87.9k22158231
87.9k22158231
Minor correction I think ... in SQLite, ISNULL should be IFNULL.
– Michael
Jan 1 '17 at 20:03
Thanks @Michael, updated with better info
– RichardTheKiwi
Jan 3 '17 at 2:49
add a comment |
Minor correction I think ... in SQLite, ISNULL should be IFNULL.
– Michael
Jan 1 '17 at 20:03
Thanks @Michael, updated with better info
– RichardTheKiwi
Jan 3 '17 at 2:49
Minor correction I think ... in SQLite, ISNULL should be IFNULL.
– Michael
Jan 1 '17 at 20:03
Minor correction I think ... in SQLite, ISNULL should be IFNULL.
– Michael
Jan 1 '17 at 20:03
Thanks @Michael, updated with better info
– RichardTheKiwi
Jan 3 '17 at 2:49
Thanks @Michael, updated with better info
– RichardTheKiwi
Jan 3 '17 at 2:49
add a comment |
You can do something like this to fake it:
select * from test
order by case ordercol when null then 1 else 0 end, ordercol
why notorder by case when ordercol is null then 0 else 1, ordercol
? saves a weird bug when someone puts values below -100 in ordercol.
– mvds
Sep 19 '12 at 21:36
That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!
– Blorgbeard
Sep 19 '12 at 21:38
add a comment |
You can do something like this to fake it:
select * from test
order by case ordercol when null then 1 else 0 end, ordercol
why notorder by case when ordercol is null then 0 else 1, ordercol
? saves a weird bug when someone puts values below -100 in ordercol.
– mvds
Sep 19 '12 at 21:36
That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!
– Blorgbeard
Sep 19 '12 at 21:38
add a comment |
You can do something like this to fake it:
select * from test
order by case ordercol when null then 1 else 0 end, ordercol
You can do something like this to fake it:
select * from test
order by case ordercol when null then 1 else 0 end, ordercol
edited Sep 19 '12 at 21:37
answered Sep 19 '12 at 21:29
BlorgbeardBlorgbeard
76.3k40197249
76.3k40197249
why notorder by case when ordercol is null then 0 else 1, ordercol
? saves a weird bug when someone puts values below -100 in ordercol.
– mvds
Sep 19 '12 at 21:36
That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!
– Blorgbeard
Sep 19 '12 at 21:38
add a comment |
why notorder by case when ordercol is null then 0 else 1, ordercol
? saves a weird bug when someone puts values below -100 in ordercol.
– mvds
Sep 19 '12 at 21:36
That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!
– Blorgbeard
Sep 19 '12 at 21:38
why not
order by case when ordercol is null then 0 else 1, ordercol
? saves a weird bug when someone puts values below -100 in ordercol.– mvds
Sep 19 '12 at 21:36
why not
order by case when ordercol is null then 0 else 1, ordercol
? saves a weird bug when someone puts values below -100 in ordercol.– mvds
Sep 19 '12 at 21:36
That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!
– Blorgbeard
Sep 19 '12 at 21:38
That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!
– Blorgbeard
Sep 19 '12 at 21:38
add a comment |
I ran into the same problem.
I found out this could work:
(I didn't find any isnull
function for SQLite)
order by ifnull(column_what_you_want_to_sort,'value in case of null')
add a comment |
I ran into the same problem.
I found out this could work:
(I didn't find any isnull
function for SQLite)
order by ifnull(column_what_you_want_to_sort,'value in case of null')
add a comment |
I ran into the same problem.
I found out this could work:
(I didn't find any isnull
function for SQLite)
order by ifnull(column_what_you_want_to_sort,'value in case of null')
I ran into the same problem.
I found out this could work:
(I didn't find any isnull
function for SQLite)
order by ifnull(column_what_you_want_to_sort,'value in case of null')
edited Aug 26 '17 at 12:59
lenooh
5,96553433
5,96553433
answered Apr 1 '16 at 13:13
linczylinczy
9614
9614
add a comment |
add a comment |
My solution was to reverse the range of my priority
column and use order by desc
(sort in descending order). This puts nulls at the end in one go.
Clearly this only works if you control your priority
ordering column, but it's pretty great if you can swing it!
If that's doesn't work, I found a union query worked. Simply union
two queries and add the constraint where not null
on the first and where null
on the second. The second query will be at the end of the first.
add a comment |
My solution was to reverse the range of my priority
column and use order by desc
(sort in descending order). This puts nulls at the end in one go.
Clearly this only works if you control your priority
ordering column, but it's pretty great if you can swing it!
If that's doesn't work, I found a union query worked. Simply union
two queries and add the constraint where not null
on the first and where null
on the second. The second query will be at the end of the first.
add a comment |
My solution was to reverse the range of my priority
column and use order by desc
(sort in descending order). This puts nulls at the end in one go.
Clearly this only works if you control your priority
ordering column, but it's pretty great if you can swing it!
If that's doesn't work, I found a union query worked. Simply union
two queries and add the constraint where not null
on the first and where null
on the second. The second query will be at the end of the first.
My solution was to reverse the range of my priority
column and use order by desc
(sort in descending order). This puts nulls at the end in one go.
Clearly this only works if you control your priority
ordering column, but it's pretty great if you can swing it!
If that's doesn't work, I found a union query worked. Simply union
two queries and add the constraint where not null
on the first and where null
on the second. The second query will be at the end of the first.
answered Feb 2 at 18:33
Evan MoranEvan Moran
2,5682616
2,5682616
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f12503120%2fhow-to-do-nulls-last-in-sqlite%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown