Select data from tables, if certain criteria is true then select max date for the records that are true
I am using tableau 10.5 custom Sql pulling from an Oracle DB and would like to query off of the five tables in the picture. I have seen posts on here about pulling data based on max date if two values are the same but what I'm looking for is a little different. I want to select:
mnemonic,
problem_id,
create_date,
env_name
but when mnemonic, problem_id, and env_name are all the same I would like to only pull the record with the latest create_date. In my actual scenario there are other values I want selected but I left those out of this post to simplify it. 1
I would greatly appreciate any help or points in the right direction!
Thanks,
Alex
sql oracle
edited Nov 15 '18 at 17:02
a_horse_with_no_name
305k46467562
asked Nov 15 '18 at 16:51
Alex GunnersonAlex Gunnerson
1
add a comment |
I am using tableau 10.5 custom Sql pulling from an Oracle DB and would like to query off of the five tables in the picture. I have seen posts on here about pulling data based on max date if two values are the same but what I'm looking for is a little different. I want to select:
mnemonic,
problem_id,
create_date,
env_name
but when mnemonic, problem_id, and env_name are all the same I would like to only pull the record with the latest create_date. In my actual scenario there are other values I want selected but I left those out of this post to simplify it. 1
I would greatly appreciate any help or points in the right direction!
Thanks,
Alex
sql oracle
edited Nov 15 '18 at 17:02
a_horse_with_no_name
305k46467562
asked Nov 15 '18 at 16:51
Alex GunnersonAlex Gunnerson
1
Hi, Please edit the question and show sample data and expected output (as tables)
– OldProgrammer
Nov 15 '18 at 18:52
add a comment |
I am using tableau 10.5 custom Sql pulling from an Oracle DB and would like to query off of the five tables in the picture. I have seen posts on here about pulling data based on max date if two values are the same but what I'm looking for is a little different. I want to select:
mnemonic,
problem_id,
create_date,
env_name
but when mnemonic, problem_id, and env_name are all the same I would like to only pull the record with the latest create_date. In my actual scenario there are other values I want selected but I left those out of this post to simplify it. 1
I would greatly appreciate any help or points in the right direction!
Thanks,
Alex
sql oracle
edited Nov 15 '18 at 17:02
a_horse_with_no_name
305k46467562
asked Nov 15 '18 at 16:51
Alex GunnersonAlex Gunnerson
1
I am using tableau 10.5 custom Sql pulling from an Oracle DB and would like to query off of the five tables in the picture. I have seen posts on here about pulling data based on max date if two values are the same but what I'm looking for is a little different. I want to select:
mnemonic,
problem_id,
create_date,
env_name
but when mnemonic, problem_id, and env_name are all the same I would like to only pull the record with the latest create_date. In my actual scenario there are other values I want selected but I left those out of this post to simplify it. 1
I would greatly appreciate any help or points in the right direction!
Thanks,
Alex
sql oracle
sql oracle
edited Nov 15 '18 at 17:02
a_horse_with_no_name
305k46467562
asked Nov 15 '18 at 16:51
Alex GunnersonAlex Gunnerson
1
edited Nov 15 '18 at 17:02
a_horse_with_no_name
305k46467562
asked Nov 15 '18 at 16:51
Alex GunnersonAlex Gunnerson
1
edited Nov 15 '18 at 17:02
a_horse_with_no_name
305k46467562
edited Nov 15 '18 at 17:02
a_horse_with_no_name
305k46467562
edited Nov 15 '18 at 17:02
a_horse_with_no_name
305k46467562
305k46467562
asked Nov 15 '18 at 16:51
Alex GunnersonAlex Gunnerson
1
asked Nov 15 '18 at 16:51
Alex GunnersonAlex Gunnerson
1
asked Nov 15 '18 at 16:51
Alex GunnersonAlex Gunnerson
1
1
Hi, Please edit the question and show sample data and expected output (as tables)
– OldProgrammer
Nov 15 '18 at 18:52
add a comment |
Hi, Please edit the question and show sample data and expected output (as tables)
– OldProgrammer
Nov 15 '18 at 18:52
Hi, Please edit the question and show sample data and expected output (as tables)
– OldProgrammer
Nov 15 '18 at 18:52
Hi, Please edit the question and show sample data and expected output (as tables)
– OldProgrammer
Nov 15 '18 at 18:52
add a comment |
1 Answer
1
active
oldest
votes
If I understand correctly, then you want to select those columns:
- MNEMONIC from TABLE4
- PROBLEM_ID from TABLE3
- biggest CREATE_DATE from CREATE_DATE when other columns are the same
- ENV_NAME from TABLE2
Simply using LEFT JOIN and GROUP BY to get what you want:
SELECT TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
MAX(TABLE1.CREATE_DATE) CREATE_DATE,
TABLE2.ENV_NAME
FROM TABLE1
LEFT JOIN TABLE2
ON (TABLE1.ENVIRONMENT_ID = TABLE2.ID)
LEFT JOIN TABLE3
ON (TABLE1.PROBLEM_ID = TABLE3.ID)
LEFT JOIN TABLE4
ON (TABLE1.MNEMONIC = TABLE4.ID)
GROUP BY TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
TABLE2.ENV_NAME;
P/s: You should review your table design. The column's name makes the viewer a little bit confuse. If I understand incorrect, then you got the idea to join and get information from multi tables.
answered Nov 16 '18 at 1:48
tungns304tungns304
7119
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%2f53324294%2fselect-data-from-tables-if-certain-criteria-is-true-then-select-max-date-for-th%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
If I understand correctly, then you want to select those columns:
- MNEMONIC from TABLE4
- PROBLEM_ID from TABLE3
- biggest CREATE_DATE from CREATE_DATE when other columns are the same
- ENV_NAME from TABLE2
Simply using LEFT JOIN and GROUP BY to get what you want:
SELECT TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
MAX(TABLE1.CREATE_DATE) CREATE_DATE,
TABLE2.ENV_NAME
FROM TABLE1
LEFT JOIN TABLE2
ON (TABLE1.ENVIRONMENT_ID = TABLE2.ID)
LEFT JOIN TABLE3
ON (TABLE1.PROBLEM_ID = TABLE3.ID)
LEFT JOIN TABLE4
ON (TABLE1.MNEMONIC = TABLE4.ID)
GROUP BY TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
TABLE2.ENV_NAME;
P/s: You should review your table design. The column's name makes the viewer a little bit confuse. If I understand incorrect, then you got the idea to join and get information from multi tables.
answered Nov 16 '18 at 1:48
tungns304tungns304
7119
add a comment |
If I understand correctly, then you want to select those columns:
- MNEMONIC from TABLE4
- PROBLEM_ID from TABLE3
- biggest CREATE_DATE from CREATE_DATE when other columns are the same
- ENV_NAME from TABLE2
Simply using LEFT JOIN and GROUP BY to get what you want:
SELECT TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
MAX(TABLE1.CREATE_DATE) CREATE_DATE,
TABLE2.ENV_NAME
FROM TABLE1
LEFT JOIN TABLE2
ON (TABLE1.ENVIRONMENT_ID = TABLE2.ID)
LEFT JOIN TABLE3
ON (TABLE1.PROBLEM_ID = TABLE3.ID)
LEFT JOIN TABLE4
ON (TABLE1.MNEMONIC = TABLE4.ID)
GROUP BY TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
TABLE2.ENV_NAME;
P/s: You should review your table design. The column's name makes the viewer a little bit confuse. If I understand incorrect, then you got the idea to join and get information from multi tables.
answered Nov 16 '18 at 1:48
tungns304tungns304
7119
add a comment |
If I understand correctly, then you want to select those columns:
- MNEMONIC from TABLE4
- PROBLEM_ID from TABLE3
- biggest CREATE_DATE from CREATE_DATE when other columns are the same
- ENV_NAME from TABLE2
Simply using LEFT JOIN and GROUP BY to get what you want:
SELECT TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
MAX(TABLE1.CREATE_DATE) CREATE_DATE,
TABLE2.ENV_NAME
FROM TABLE1
LEFT JOIN TABLE2
ON (TABLE1.ENVIRONMENT_ID = TABLE2.ID)
LEFT JOIN TABLE3
ON (TABLE1.PROBLEM_ID = TABLE3.ID)
LEFT JOIN TABLE4
ON (TABLE1.MNEMONIC = TABLE4.ID)
GROUP BY TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
TABLE2.ENV_NAME;
P/s: You should review your table design. The column's name makes the viewer a little bit confuse. If I understand incorrect, then you got the idea to join and get information from multi tables.
answered Nov 16 '18 at 1:48
tungns304tungns304
7119
If I understand correctly, then you want to select those columns:
- MNEMONIC from TABLE4
- PROBLEM_ID from TABLE3
- biggest CREATE_DATE from CREATE_DATE when other columns are the same
- ENV_NAME from TABLE2
Simply using LEFT JOIN and GROUP BY to get what you want:
SELECT TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
MAX(TABLE1.CREATE_DATE) CREATE_DATE,
TABLE2.ENV_NAME
FROM TABLE1
LEFT JOIN TABLE2
ON (TABLE1.ENVIRONMENT_ID = TABLE2.ID)
LEFT JOIN TABLE3
ON (TABLE1.PROBLEM_ID = TABLE3.ID)
LEFT JOIN TABLE4
ON (TABLE1.MNEMONIC = TABLE4.ID)
GROUP BY TABLE4.MNEMONIC,
TABLE3.PROBLEM_ID,
TABLE2.ENV_NAME;
P/s: You should review your table design. The column's name makes the viewer a little bit confuse. If I understand incorrect, then you got the idea to join and get information from multi tables.
answered Nov 16 '18 at 1:48
tungns304tungns304
7119
edited Nov 16 '18 at 2:02
answered Nov 16 '18 at 1:48
tungns304tungns304
7119
answered Nov 16 '18 at 1:48
tungns304tungns304
7119
answered Nov 16 '18 at 1:48
tungns304tungns304
7119
7119
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%2f53324294%2fselect-data-from-tables-if-certain-criteria-is-true-then-select-max-date-for-th%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
Hi, Please edit the question and show sample data and expected output (as tables)
– OldProgrammer
Nov 15 '18 at 18:52