Select unique records from multiple records based on max date The Next CEO of Stack...
What is meant by a M next to a roman numeral?
Rotate a column
If I blow insulation everywhere in my attic except the door trap, will heat escape through it?
Solution of this Diophantine Equation
Whats the best way to handle refactoring a big file?
Trouble understanding the speech of overseas colleagues
Inappropriate reference requests from Journal reviewers
I believe this to be a fraud - hired, then asked to cash check and send cash as Bitcoin
Can I equip Skullclamp on a creature I am sacrificing?
How do I go from 300 unfinished/half written blog posts, to published posts?
Putting a 2D region plot under a 3D plot
How to count occurrences of text in a file?
Science fiction (dystopian) short story set after WWIII
Can a single photon have an energy density?
What is the point of a new vote on May's deal when the indicative votes suggest she will not win?
Why does standard notation not preserve intervals (visually)
Horror movie/show or scene where a horse creature opens its mouth really wide and devours a man in a stables
Anatomically Correct Mesopelagic Aves
How to safely derail a train during transit?
Too much space between section and text in a twocolumn document
How to make a software documentation "officially" citable?
Fastest way to shutdown Ubuntu Mate 18.10
Return of the Riley Riddles in Reverse
Why were Madagascar and New Zealand discovered so late?
Select unique records from multiple records based on max date
The Next CEO of Stack OverflowSelect which has max date or latest dateSelect rows based on latest date with multiple joinsSelect the max from two max valuesReturn Distinct Set of Values Based on MAX DateHow can i return the date of max id?Find max and min date over multiple fields of the same recordMIN / MAX values with DateWhy is Max Date not working?MySQL query: select max(A) and use MAX(pkey) if multiple max(A) records existselect MAX() from MySQL view (2x INNER JOIN) is slow
I have table like below
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct | 1293 |
| 1 | BILL | Y | 10-Oct | 1357 |
| 1 | BILL | N | 10-Sep | 13567 |
| 1 | PHYSICAL | Y | 10-Oct | 8403 |
| 1 | BILL | Y | 6-Oct | 135 |
| 2 | PHYSICAL | Y | 9-Oct | 5333 |
| 2 | BILL | N | 8-Aug | 657 |
| 2 | BILL | Y | 8-Oct | 3673 |
| 2 | BILL | Y | 10-Oct | 2574 |
| 3 | BILL | Y | 11-Oct | 57923 |
| 3 | BILL | Y | 11-Oct | 29384 |
+----+----------+-----+-------------+--------+
need to check below multiple conditions before populating the data
- If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
- If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).
Below was the expected result.
Can anyone plug me some ideas to achieve this.
here is the sqlfiddle: http://sqlfiddle.com/#!4/d0098/1
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct | 1293 |
| 1 | BILL | Y | 10-Oct | 1357 |
| 1 | BILL | N | 10-Sep | 13567 |
| 2 | PHYSICAL | Y | 9-Oct | 5333 |
| 2 | BILL | N | 8-Aug | 657 |
| 2 | BILL | Y | 10-Oct | 2574 |
| 3 | BILL | Y | 11-Oct | 29384 |
+----+----------+-----+-------------+--------+
oracle group-by max
edited Feb 11 at 10:14
a_horse_with_no_name
41.2k778115
asked Oct 11 '18 at 21:40
heyeheye
5618
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have table like below
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct | 1293 |
| 1 | BILL | Y | 10-Oct | 1357 |
| 1 | BILL | N | 10-Sep | 13567 |
| 1 | PHYSICAL | Y | 10-Oct | 8403 |
| 1 | BILL | Y | 6-Oct | 135 |
| 2 | PHYSICAL | Y | 9-Oct | 5333 |
| 2 | BILL | N | 8-Aug | 657 |
| 2 | BILL | Y | 8-Oct | 3673 |
| 2 | BILL | Y | 10-Oct | 2574 |
| 3 | BILL | Y | 11-Oct | 57923 |
| 3 | BILL | Y | 11-Oct | 29384 |
+----+----------+-----+-------------+--------+
need to check below multiple conditions before populating the data
- If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
- If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).
Below was the expected result.
Can anyone plug me some ideas to achieve this.
here is the sqlfiddle: http://sqlfiddle.com/#!4/d0098/1
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct | 1293 |
| 1 | BILL | Y | 10-Oct | 1357 |
| 1 | BILL | N | 10-Sep | 13567 |
| 2 | PHYSICAL | Y | 9-Oct | 5333 |
| 2 | BILL | N | 8-Aug | 657 |
| 2 | BILL | Y | 10-Oct | 2574 |
| 3 | BILL | Y | 11-Oct | 29384 |
+----+----------+-----+-------------+--------+
oracle group-by max
edited Feb 11 at 10:14
a_horse_with_no_name
41.2k778115
asked Oct 11 '18 at 21:40
heyeheye
5618
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
CalculateROW_NUMBER() OVER (PARTITION BY id, addr, ind ORDER BY update_date DESC)in CTE and select only records where the calculated value is 1.
– Akina
Oct 12 '18 at 5:34
add a comment |
I have table like below
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct | 1293 |
| 1 | BILL | Y | 10-Oct | 1357 |
| 1 | BILL | N | 10-Sep | 13567 |
| 1 | PHYSICAL | Y | 10-Oct | 8403 |
| 1 | BILL | Y | 6-Oct | 135 |
| 2 | PHYSICAL | Y | 9-Oct | 5333 |
| 2 | BILL | N | 8-Aug | 657 |
| 2 | BILL | Y | 8-Oct | 3673 |
| 2 | BILL | Y | 10-Oct | 2574 |
| 3 | BILL | Y | 11-Oct | 57923 |
| 3 | BILL | Y | 11-Oct | 29384 |
+----+----------+-----+-------------+--------+
need to check below multiple conditions before populating the data
- If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
- If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).
Below was the expected result.
Can anyone plug me some ideas to achieve this.
here is the sqlfiddle: http://sqlfiddle.com/#!4/d0098/1
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct | 1293 |
| 1 | BILL | Y | 10-Oct | 1357 |
| 1 | BILL | N | 10-Sep | 13567 |
| 2 | PHYSICAL | Y | 9-Oct | 5333 |
| 2 | BILL | N | 8-Aug | 657 |
| 2 | BILL | Y | 10-Oct | 2574 |
| 3 | BILL | Y | 11-Oct | 29384 |
+----+----------+-----+-------------+--------+
oracle group-by max
edited Feb 11 at 10:14
a_horse_with_no_name
41.2k778115
asked Oct 11 '18 at 21:40
heyeheye
5618
I have table like below
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct | 1293 |
| 1 | BILL | Y | 10-Oct | 1357 |
| 1 | BILL | N | 10-Sep | 13567 |
| 1 | PHYSICAL | Y | 10-Oct | 8403 |
| 1 | BILL | Y | 6-Oct | 135 |
| 2 | PHYSICAL | Y | 9-Oct | 5333 |
| 2 | BILL | N | 8-Aug | 657 |
| 2 | BILL | Y | 8-Oct | 3673 |
| 2 | BILL | Y | 10-Oct | 2574 |
| 3 | BILL | Y | 11-Oct | 57923 |
| 3 | BILL | Y | 11-Oct | 29384 |
+----+----------+-----+-------------+--------+
need to check below multiple conditions before populating the data
- If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
- If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).
Below was the expected result.
Can anyone plug me some ideas to achieve this.
here is the sqlfiddle: http://sqlfiddle.com/#!4/d0098/1
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct | 1293 |
| 1 | BILL | Y | 10-Oct | 1357 |
| 1 | BILL | N | 10-Sep | 13567 |
| 2 | PHYSICAL | Y | 9-Oct | 5333 |
| 2 | BILL | N | 8-Aug | 657 |
| 2 | BILL | Y | 10-Oct | 2574 |
| 3 | BILL | Y | 11-Oct | 29384 |
+----+----------+-----+-------------+--------+
oracle group-by max
oracle group-by max
edited Feb 11 at 10:14
a_horse_with_no_name
41.2k778115
asked Oct 11 '18 at 21:40
heyeheye
5618
edited Feb 11 at 10:14
a_horse_with_no_name
41.2k778115
asked Oct 11 '18 at 21:40
heyeheye
5618
edited Feb 11 at 10:14
a_horse_with_no_name
41.2k778115
edited Feb 11 at 10:14
a_horse_with_no_name
41.2k778115
edited Feb 11 at 10:14
a_horse_with_no_name
41.2k778115
41.2k778115
asked Oct 11 '18 at 21:40
heyeheye
5618
asked Oct 11 '18 at 21:40
heyeheye
5618
asked Oct 11 '18 at 21:40
heyeheye
5618
5618
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
CalculateROW_NUMBER() OVER (PARTITION BY id, addr, ind ORDER BY update_date DESC)in CTE and select only records where the calculated value is 1.
– Akina
Oct 12 '18 at 5:34
add a comment |
1
CalculateROW_NUMBER() OVER (PARTITION BY id, addr, ind ORDER BY update_date DESC)in CTE and select only records where the calculated value is 1.
– Akina
Oct 12 '18 at 5:34
1
1
Calculate
ROW_NUMBER() OVER (PARTITION BY id, addr, ind ORDER BY update_date DESC) in CTE and select only records where the calculated value is 1.– Akina
Oct 12 '18 at 5:34
Calculate
ROW_NUMBER() OVER (PARTITION BY id, addr, ind ORDER BY update_date DESC) in CTE and select only records where the calculated value is 1.– Akina
Oct 12 '18 at 5:34
add a comment |
1 Answer
1
active
oldest
votes
For the first condition
- count() Over (Partition by id, addr, Ind)
- max(update_date) Over (Partition by id, addr, Ind)
For the secontd condition
- row_number Over (Partition by id, addr, Ind and update_Date)
answered Oct 12 '18 at 21:24
Claudiu RaduClaudiu Radu
65
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f219908%2fselect-unique-records-from-multiple-records-based-on-max-date%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
For the first condition
- count() Over (Partition by id, addr, Ind)
- max(update_date) Over (Partition by id, addr, Ind)
For the secontd condition
- row_number Over (Partition by id, addr, Ind and update_Date)
answered Oct 12 '18 at 21:24
Claudiu RaduClaudiu Radu
65
add a comment |
For the first condition
- count() Over (Partition by id, addr, Ind)
- max(update_date) Over (Partition by id, addr, Ind)
For the secontd condition
- row_number Over (Partition by id, addr, Ind and update_Date)
answered Oct 12 '18 at 21:24
Claudiu RaduClaudiu Radu
65
add a comment |
For the first condition
- count() Over (Partition by id, addr, Ind)
- max(update_date) Over (Partition by id, addr, Ind)
For the secontd condition
- row_number Over (Partition by id, addr, Ind and update_Date)
answered Oct 12 '18 at 21:24
Claudiu RaduClaudiu Radu
65
For the first condition
- count() Over (Partition by id, addr, Ind)
- max(update_date) Over (Partition by id, addr, Ind)
For the secontd condition
- row_number Over (Partition by id, addr, Ind and update_Date)
answered Oct 12 '18 at 21:24
Claudiu RaduClaudiu Radu
65
answered Oct 12 '18 at 21:24
Claudiu RaduClaudiu Radu
65
answered Oct 12 '18 at 21:24
Claudiu RaduClaudiu Radu
65
answered Oct 12 '18 at 21:24
Claudiu RaduClaudiu Radu
65
65
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f219908%2fselect-unique-records-from-multiple-records-based-on-max-date%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
1
Calculate
ROW_NUMBER() OVER (PARTITION BY id, addr, ind ORDER BY update_date DESC)in CTE and select only records where the calculated value is 1.– Akina
Oct 12 '18 at 5:34