Mixing
delete “duplicate” entries and keep latest one
0
I forgot to add a unique index (process_id, result_id) to my table:
id, created_at, process_id, result_id, value
1, 2018-11-01, 1, 1, a
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c
now I have multiple entries for the same combination of process_id and result_id.
In order to add the unique index, I need to get rid of the duplicates and only keep the latest entry (created_at) for each process/result pair.
How can I easily identify and delete the older duplicates?
I can identify all duplicates:
select pr.id, pr.created_at, prd.*
from process_results pr
join (
select process_id, result_id
from process_results
group by process_id, result_id
having count(*) > 1
) prd on pr.request_id = prd.request_id and pr.request_filter_id = prd.request_filter_id
but I am not sure how to delete all but the latest ones
expected result:
id, created_at, process_id, result_id, value
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c
mysql
asked Nov 19 '18 at 19:22
ChrisChris
2,33131948
add a comment |
0
I forgot to add a unique index (process_id, result_id) to my table:
id, created_at, process_id, result_id, value
1, 2018-11-01, 1, 1, a
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c
now I have multiple entries for the same combination of process_id and result_id.
In order to add the unique index, I need to get rid of the duplicates and only keep the latest entry (created_at) for each process/result pair.
How can I easily identify and delete the older duplicates?
I can identify all duplicates:
select pr.id, pr.created_at, prd.*
from process_results pr
join (
select process_id, result_id
from process_results
group by process_id, result_id
having count(*) > 1
) prd on pr.request_id = prd.request_id and pr.request_filter_id = prd.request_filter_id
but I am not sure how to delete all but the latest ones
expected result:
id, created_at, process_id, result_id, value
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c
mysql
asked Nov 19 '18 at 19:22
ChrisChris
2,33131948
1
Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 19:34
add a comment |
0
0
0
I forgot to add a unique index (process_id, result_id) to my table:
id, created_at, process_id, result_id, value
1, 2018-11-01, 1, 1, a
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c
now I have multiple entries for the same combination of process_id and result_id.
In order to add the unique index, I need to get rid of the duplicates and only keep the latest entry (created_at) for each process/result pair.
How can I easily identify and delete the older duplicates?
I can identify all duplicates:
select pr.id, pr.created_at, prd.*
from process_results pr
join (
select process_id, result_id
from process_results
group by process_id, result_id
having count(*) > 1
) prd on pr.request_id = prd.request_id and pr.request_filter_id = prd.request_filter_id
but I am not sure how to delete all but the latest ones
expected result:
id, created_at, process_id, result_id, value
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c
mysql
asked Nov 19 '18 at 19:22
ChrisChris
2,33131948
I forgot to add a unique index (process_id, result_id) to my table:
id, created_at, process_id, result_id, value
1, 2018-11-01, 1, 1, a
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c
now I have multiple entries for the same combination of process_id and result_id.
In order to add the unique index, I need to get rid of the duplicates and only keep the latest entry (created_at) for each process/result pair.
How can I easily identify and delete the older duplicates?
I can identify all duplicates:
select pr.id, pr.created_at, prd.*
from process_results pr
join (
select process_id, result_id
from process_results
group by process_id, result_id
having count(*) > 1
) prd on pr.request_id = prd.request_id and pr.request_filter_id = prd.request_filter_id
but I am not sure how to delete all but the latest ones
expected result:
id, created_at, process_id, result_id, value
2, 2018-11-03, 1, 1, b
3, 2018-11-05, 2, 4, c
mysql
mysql
asked Nov 19 '18 at 19:22
ChrisChris
2,33131948
asked Nov 19 '18 at 19:22
ChrisChris
2,33131948
edited Nov 19 '18 at 19:38
Chris
asked Nov 19 '18 at 19:22
ChrisChris
2,33131948
asked Nov 19 '18 at 19:22
ChrisChris
2,33131948
asked Nov 19 '18 at 19:22
ChrisChris
2,33131948
2,33131948
1
Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 19:34
add a comment |
1
Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 19:34
1
1
Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 19:34
Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 19:34
add a comment |
2 Answers
2
active
oldest
votes
0
DELETE t1
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
;
To identify is the same
SELECT t1.*
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
answered Nov 19 '18 at 19:37
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
Is there a reason you used WHERE instead of ON?
– Uueerdo
Nov 19 '18 at 19:45
@Uueerdo Not really. I just copy paste from an example. Work the same both ways
– Juan Carlos Oropeza
Nov 19 '18 at 19:47
What happen chris I think you check this as the correct answer but then you remove it? Doesnt solve your problem?
– Juan Carlos Oropeza
Nov 20 '18 at 13:47
add a comment |
0
DELETE bad_rows.* FROM process_results AS bad_rows
INNER JOIN (
SELECT created_at,value,min(`process_id`) AS min_id FROM process_results GROUP BY process_id,result_id HAVING COUNT(*) > 1
) AS good_rows ON good_rows.value = bad_rows.value AND good_rows.min_id != bad_rows.id
answered Nov 19 '18 at 20:10
AlexeyAlexey
1,93242036
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
});
}
});
draft saved
draft discarded
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%2f53381290%2fdelete-duplicate-entries-and-keep-latest-one%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
DELETE t1
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
;
To identify is the same
SELECT t1.*
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
answered Nov 19 '18 at 19:37
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
Is there a reason you used WHERE instead of ON?
– Uueerdo
Nov 19 '18 at 19:45
@Uueerdo Not really. I just copy paste from an example. Work the same both ways
– Juan Carlos Oropeza
Nov 19 '18 at 19:47
What happen chris I think you check this as the correct answer but then you remove it? Doesnt solve your problem?
– Juan Carlos Oropeza
Nov 20 '18 at 13:47
add a comment |
0
DELETE t1
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
;
To identify is the same
SELECT t1.*
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
answered Nov 19 '18 at 19:37
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
Is there a reason you used WHERE instead of ON?
– Uueerdo
Nov 19 '18 at 19:45
@Uueerdo Not really. I just copy paste from an example. Work the same both ways
– Juan Carlos Oropeza
Nov 19 '18 at 19:47
What happen chris I think you check this as the correct answer but then you remove it? Doesnt solve your problem?
– Juan Carlos Oropeza
Nov 20 '18 at 13:47
add a comment |
0
0
0
DELETE t1
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
;
To identify is the same
SELECT t1.*
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
answered Nov 19 '18 at 19:37
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
DELETE t1
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
;
To identify is the same
SELECT t1.*
FROM yourTable t1
INNER JOIN yourTable t2
ON
t1.created_at < t2.created_at
AND t1.process_id = t2.process_id
AND t1.result_id = t2.result_id
answered Nov 19 '18 at 19:37
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
edited Nov 19 '18 at 19:54
answered Nov 19 '18 at 19:37
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
answered Nov 19 '18 at 19:37
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
answered Nov 19 '18 at 19:37
Juan Carlos OropezaJuan Carlos Oropeza
36k63876
36k63876
Is there a reason you used WHERE instead of ON?
– Uueerdo
Nov 19 '18 at 19:45
@Uueerdo Not really. I just copy paste from an example. Work the same both ways
– Juan Carlos Oropeza
Nov 19 '18 at 19:47
What happen chris I think you check this as the correct answer but then you remove it? Doesnt solve your problem?
– Juan Carlos Oropeza
Nov 20 '18 at 13:47
add a comment |
Is there a reason you used WHERE instead of ON?
– Uueerdo
Nov 19 '18 at 19:45
@Uueerdo Not really. I just copy paste from an example. Work the same both ways
– Juan Carlos Oropeza
Nov 19 '18 at 19:47
What happen chris I think you check this as the correct answer but then you remove it? Doesnt solve your problem?
– Juan Carlos Oropeza
Nov 20 '18 at 13:47
Is there a reason you used WHERE instead of ON?
– Uueerdo
Nov 19 '18 at 19:45
Is there a reason you used WHERE instead of ON?
– Uueerdo
Nov 19 '18 at 19:45
@Uueerdo Not really. I just copy paste from an example. Work the same both ways
– Juan Carlos Oropeza
Nov 19 '18 at 19:47
@Uueerdo Not really. I just copy paste from an example. Work the same both ways
– Juan Carlos Oropeza
Nov 19 '18 at 19:47
What happen chris I think you check this as the correct answer but then you remove it? Doesnt solve your problem?
– Juan Carlos Oropeza
Nov 20 '18 at 13:47
What happen chris I think you check this as the correct answer but then you remove it? Doesnt solve your problem?
– Juan Carlos Oropeza
Nov 20 '18 at 13:47
add a comment |
0
DELETE bad_rows.* FROM process_results AS bad_rows
INNER JOIN (
SELECT created_at,value,min(`process_id`) AS min_id FROM process_results GROUP BY process_id,result_id HAVING COUNT(*) > 1
) AS good_rows ON good_rows.value = bad_rows.value AND good_rows.min_id != bad_rows.id
answered Nov 19 '18 at 20:10
AlexeyAlexey
1,93242036
add a comment |
0
DELETE bad_rows.* FROM process_results AS bad_rows
INNER JOIN (
SELECT created_at,value,min(`process_id`) AS min_id FROM process_results GROUP BY process_id,result_id HAVING COUNT(*) > 1
) AS good_rows ON good_rows.value = bad_rows.value AND good_rows.min_id != bad_rows.id
answered Nov 19 '18 at 20:10
AlexeyAlexey
1,93242036
add a comment |
0
0
0
DELETE bad_rows.* FROM process_results AS bad_rows
INNER JOIN (
SELECT created_at,value,min(`process_id`) AS min_id FROM process_results GROUP BY process_id,result_id HAVING COUNT(*) > 1
) AS good_rows ON good_rows.value = bad_rows.value AND good_rows.min_id != bad_rows.id
answered Nov 19 '18 at 20:10
AlexeyAlexey
1,93242036
DELETE bad_rows.* FROM process_results AS bad_rows
INNER JOIN (
SELECT created_at,value,min(`process_id`) AS min_id FROM process_results GROUP BY process_id,result_id HAVING COUNT(*) > 1
) AS good_rows ON good_rows.value = bad_rows.value AND good_rows.min_id != bad_rows.id
answered Nov 19 '18 at 20:10
AlexeyAlexey
1,93242036
answered Nov 19 '18 at 20:10
AlexeyAlexey
1,93242036
answered Nov 19 '18 at 20:10
AlexeyAlexey
1,93242036
answered Nov 19 '18 at 20:10
AlexeyAlexey
1,93242036
1,93242036
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53381290%2fdelete-duplicate-entries-and-keep-latest-one%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
Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 19 '18 at 19:34