Is there a way to get max function to ignore nulls within a column in sqlite?
Simply put, if I have a table with two columns (say people and height), I can use select max(height) from 'table' group by people and it will yield the largest height for any group of people_ignoring any nulls_.
If, however, I have a table with more columns (height_1, height_2, height_3, etc.) and I want to simply reproduce that table with a new column showing the maximum height (height_1, height_2, height_3, max_height), I would intuitvely like to put.
select
height_1,
height_2,
height_3,
max(height_1, height_2, height_3) as max_height
from 'table'
but used this way, max seems to behave differently, returning null if any of the arguments are null.
Is there some form of use which will get it to behave the way it does when aggregating a column, and ignore null values?, even when aggregating single values across a range of columns?
sqlite
edited Nov 12 '18 at 7:11
a_horse_with_no_name
292k46446541
asked Nov 12 '18 at 7:05
Isaacson
966
add a comment |
Simply put, if I have a table with two columns (say people and height), I can use select max(height) from 'table' group by people and it will yield the largest height for any group of people_ignoring any nulls_.
If, however, I have a table with more columns (height_1, height_2, height_3, etc.) and I want to simply reproduce that table with a new column showing the maximum height (height_1, height_2, height_3, max_height), I would intuitvely like to put.
select
height_1,
height_2,
height_3,
max(height_1, height_2, height_3) as max_height
from 'table'
but used this way, max seems to behave differently, returning null if any of the arguments are null.
Is there some form of use which will get it to behave the way it does when aggregating a column, and ignore null values?, even when aggregating single values across a range of columns?
sqlite
edited Nov 12 '18 at 7:11
a_horse_with_no_name
292k46446541
asked Nov 12 '18 at 7:05
Isaacson
966
add a comment |
Simply put, if I have a table with two columns (say people and height), I can use select max(height) from 'table' group by people and it will yield the largest height for any group of people_ignoring any nulls_.
If, however, I have a table with more columns (height_1, height_2, height_3, etc.) and I want to simply reproduce that table with a new column showing the maximum height (height_1, height_2, height_3, max_height), I would intuitvely like to put.
select
height_1,
height_2,
height_3,
max(height_1, height_2, height_3) as max_height
from 'table'
but used this way, max seems to behave differently, returning null if any of the arguments are null.
Is there some form of use which will get it to behave the way it does when aggregating a column, and ignore null values?, even when aggregating single values across a range of columns?
sqlite
edited Nov 12 '18 at 7:11
a_horse_with_no_name
292k46446541
asked Nov 12 '18 at 7:05
Isaacson
966
Simply put, if I have a table with two columns (say people and height), I can use select max(height) from 'table' group by people and it will yield the largest height for any group of people_ignoring any nulls_.
If, however, I have a table with more columns (height_1, height_2, height_3, etc.) and I want to simply reproduce that table with a new column showing the maximum height (height_1, height_2, height_3, max_height), I would intuitvely like to put.
select
height_1,
height_2,
height_3,
max(height_1, height_2, height_3) as max_height
from 'table'
but used this way, max seems to behave differently, returning null if any of the arguments are null.
Is there some form of use which will get it to behave the way it does when aggregating a column, and ignore null values?, even when aggregating single values across a range of columns?
sqlite
sqlite
edited Nov 12 '18 at 7:11
a_horse_with_no_name
292k46446541
asked Nov 12 '18 at 7:05
Isaacson
966
edited Nov 12 '18 at 7:11
a_horse_with_no_name
292k46446541
asked Nov 12 '18 at 7:05
Isaacson
966
edited Nov 12 '18 at 7:11
a_horse_with_no_name
292k46446541
edited Nov 12 '18 at 7:11
a_horse_with_no_name
292k46446541
edited Nov 12 '18 at 7:11
a_horse_with_no_name
292k46446541
292k46446541
asked Nov 12 '18 at 7:05
Isaacson
966
asked Nov 12 '18 at 7:05
Isaacson
966
asked Nov 12 '18 at 7:05
Isaacson
966
966
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can use something like:
SELECT height_1
, height_2
, height_3
, max(ifnull(height_1, 0), ifnull(height_2, 0), ifnull(height_3, 0)) AS max_height
FROM "table"
answered Nov 12 '18 at 7:29
Shawn
3,5731613
Thanks. Follow up question, is there a way to do the same with min()? the problem here being that replacing nulls with '0' would give a false impression from the set {2,4,null,5,null,7} that the minimum was 0 when in fact the minimum non-null value is 2.
– Isaacson
Nov 12 '18 at 7:51
1
@Isaacson Just use a larger-than-possible number formin(), and a smaller-than-possible one formax()(And check if your select returned that number to handle cases of all values being null).
– Shawn
Nov 12 '18 at 8:05
That's perfect, thanks.
– Isaacson
Nov 12 '18 at 8:10
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%2f53257306%2fis-there-a-way-to-get-max-function-to-ignore-nulls-within-a-column-in-sqlite%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use something like:
SELECT height_1
, height_2
, height_3
, max(ifnull(height_1, 0), ifnull(height_2, 0), ifnull(height_3, 0)) AS max_height
FROM "table"
answered Nov 12 '18 at 7:29
Shawn
3,5731613
Thanks. Follow up question, is there a way to do the same with min()? the problem here being that replacing nulls with '0' would give a false impression from the set {2,4,null,5,null,7} that the minimum was 0 when in fact the minimum non-null value is 2.
– Isaacson
Nov 12 '18 at 7:51
1
@Isaacson Just use a larger-than-possible number formin(), and a smaller-than-possible one formax()(And check if your select returned that number to handle cases of all values being null).
– Shawn
Nov 12 '18 at 8:05
That's perfect, thanks.
– Isaacson
Nov 12 '18 at 8:10
add a comment |
You can use something like:
SELECT height_1
, height_2
, height_3
, max(ifnull(height_1, 0), ifnull(height_2, 0), ifnull(height_3, 0)) AS max_height
FROM "table"
answered Nov 12 '18 at 7:29
Shawn
3,5731613
Thanks. Follow up question, is there a way to do the same with min()? the problem here being that replacing nulls with '0' would give a false impression from the set {2,4,null,5,null,7} that the minimum was 0 when in fact the minimum non-null value is 2.
– Isaacson
Nov 12 '18 at 7:51
1
@Isaacson Just use a larger-than-possible number formin(), and a smaller-than-possible one formax()(And check if your select returned that number to handle cases of all values being null).
– Shawn
Nov 12 '18 at 8:05
That's perfect, thanks.
– Isaacson
Nov 12 '18 at 8:10
add a comment |
You can use something like:
SELECT height_1
, height_2
, height_3
, max(ifnull(height_1, 0), ifnull(height_2, 0), ifnull(height_3, 0)) AS max_height
FROM "table"
answered Nov 12 '18 at 7:29
Shawn
3,5731613
You can use something like:
SELECT height_1
, height_2
, height_3
, max(ifnull(height_1, 0), ifnull(height_2, 0), ifnull(height_3, 0)) AS max_height
FROM "table"
answered Nov 12 '18 at 7:29
Shawn
3,5731613
answered Nov 12 '18 at 7:29
Shawn
3,5731613
answered Nov 12 '18 at 7:29
Shawn
3,5731613
answered Nov 12 '18 at 7:29
Shawn
3,5731613
3,5731613
Thanks. Follow up question, is there a way to do the same with min()? the problem here being that replacing nulls with '0' would give a false impression from the set {2,4,null,5,null,7} that the minimum was 0 when in fact the minimum non-null value is 2.
– Isaacson
Nov 12 '18 at 7:51
1
@Isaacson Just use a larger-than-possible number formin(), and a smaller-than-possible one formax()(And check if your select returned that number to handle cases of all values being null).
– Shawn
Nov 12 '18 at 8:05
That's perfect, thanks.
– Isaacson
Nov 12 '18 at 8:10
add a comment |
Thanks. Follow up question, is there a way to do the same with min()? the problem here being that replacing nulls with '0' would give a false impression from the set {2,4,null,5,null,7} that the minimum was 0 when in fact the minimum non-null value is 2.
– Isaacson
Nov 12 '18 at 7:51
1
@Isaacson Just use a larger-than-possible number formin(), and a smaller-than-possible one formax()(And check if your select returned that number to handle cases of all values being null).
– Shawn
Nov 12 '18 at 8:05
That's perfect, thanks.
– Isaacson
Nov 12 '18 at 8:10
Thanks. Follow up question, is there a way to do the same with min()? the problem here being that replacing nulls with '0' would give a false impression from the set {2,4,null,5,null,7} that the minimum was 0 when in fact the minimum non-null value is 2.
– Isaacson
Nov 12 '18 at 7:51
Thanks. Follow up question, is there a way to do the same with min()? the problem here being that replacing nulls with '0' would give a false impression from the set {2,4,null,5,null,7} that the minimum was 0 when in fact the minimum non-null value is 2.
– Isaacson
Nov 12 '18 at 7:51
1
1
@Isaacson Just use a larger-than-possible number for
min(), and a smaller-than-possible one for max() (And check if your select returned that number to handle cases of all values being null).– Shawn
Nov 12 '18 at 8:05
@Isaacson Just use a larger-than-possible number for
min(), and a smaller-than-possible one for max() (And check if your select returned that number to handle cases of all values being null).– Shawn
Nov 12 '18 at 8:05
That's perfect, thanks.
– Isaacson
Nov 12 '18 at 8:10
That's perfect, thanks.
– Isaacson
Nov 12 '18 at 8:10
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53257306%2fis-there-a-way-to-get-max-function-to-ignore-nulls-within-a-column-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
