MSSQL: Search for duplicates in columns based on criteria
I am trying to create a script that will look through the sql columns and search for duplicates based on first name, last name, and date of birth. Can you advice me how I can start this? Like I can do it for one id, but I need to go through the enter list of ids and do the search
id Forename Surname DateofBirth
1 John Doe 2015-05-16
2 Martin Rocks 2015-04-18
3 John Doe 2015-05-16
4 Ben Dover 2014-08-09
So in this case, I just want to write a script that can take each ID and look for duplicates based on matching Forename, Surname and Date of birth
sql
sql-server edited Nov 15 '18 at 11:16
a_horse_with_no_name
302k46463558
asked Nov 15 '18 at 10:59
edcoderedcoder
217112
add a comment |
I am trying to create a script that will look through the sql columns and search for duplicates based on first name, last name, and date of birth. Can you advice me how I can start this? Like I can do it for one id, but I need to go through the enter list of ids and do the search
id Forename Surname DateofBirth
1 John Doe 2015-05-16
2 Martin Rocks 2015-04-18
3 John Doe 2015-05-16
4 Ben Dover 2014-08-09
So in this case, I just want to write a script that can take each ID and look for duplicates based on matching Forename, Surname and Date of birth
sql
sql-server edited Nov 15 '18 at 11:16
a_horse_with_no_name
302k46463558
asked Nov 15 '18 at 10:59
edcoderedcoder
217112
add a comment |
I am trying to create a script that will look through the sql columns and search for duplicates based on first name, last name, and date of birth. Can you advice me how I can start this? Like I can do it for one id, but I need to go through the enter list of ids and do the search
id Forename Surname DateofBirth
1 John Doe 2015-05-16
2 Martin Rocks 2015-04-18
3 John Doe 2015-05-16
4 Ben Dover 2014-08-09
So in this case, I just want to write a script that can take each ID and look for duplicates based on matching Forename, Surname and Date of birth
sql
sql-server edited Nov 15 '18 at 11:16
a_horse_with_no_name
302k46463558
asked Nov 15 '18 at 10:59
edcoderedcoder
217112
I am trying to create a script that will look through the sql columns and search for duplicates based on first name, last name, and date of birth. Can you advice me how I can start this? Like I can do it for one id, but I need to go through the enter list of ids and do the search
id Forename Surname DateofBirth
1 John Doe 2015-05-16
2 Martin Rocks 2015-04-18
3 John Doe 2015-05-16
4 Ben Dover 2014-08-09
So in this case, I just want to write a script that can take each ID and look for duplicates based on matching Forename, Surname and Date of birth
sql
sql-server sql
sql-server edited Nov 15 '18 at 11:16
a_horse_with_no_name
302k46463558
asked Nov 15 '18 at 10:59
edcoderedcoder
217112
edited Nov 15 '18 at 11:16
a_horse_with_no_name
302k46463558
asked Nov 15 '18 at 10:59
edcoderedcoder
217112
edited Nov 15 '18 at 11:16
a_horse_with_no_name
302k46463558
edited Nov 15 '18 at 11:16
a_horse_with_no_name
302k46463558
edited Nov 15 '18 at 11:16
a_horse_with_no_name
302k46463558
302k46463558
asked Nov 15 '18 at 10:59
edcoderedcoder
217112
asked Nov 15 '18 at 10:59
edcoderedcoder
217112
asked Nov 15 '18 at 10:59
edcoderedcoder
217112
217112
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
you can use analytical function to achieve your goal like this:
select * from (
select ROW_NUMBER() OVER(PARTITION BY Forename, Surname, DateofBirth order by ID) RN,
Forename,
Surname,
DateofBirth
from table_name) x
where x.rn>1
answered Nov 15 '18 at 11:07
irakliG.irakliG.
1668
Perfect. This worked for me. Thank You
– edcoder
Nov 15 '18 at 11:17
add a comment |
use corelated subquery
select t.* from table_name t where
exists ( select 1 from table_name t1 where t1.DateofBirth=t.DateofBirth and t1.Forename=t.Forename
and t1.surname=t.surname
group by t1.DateofBirth,t1.Forename,t1.Surname
having count(*)>1 )
answered Nov 15 '18 at 11:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
add a comment |
You can use exists :
select t.*
from table t
where exists (select 1
from table t1
where t1.Forename = t.Forename and t1.Surname = t.Surname and
t1.DateofBirth = t.DateofBirth and t1.id <> t.id
)
answered Nov 15 '18 at 11:03
Yogesh SharmaYogesh Sharma
32.8k51438
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%2f53317941%2fmssql-search-for-duplicates-in-columns-based-on-criteria%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
you can use analytical function to achieve your goal like this:
select * from (
select ROW_NUMBER() OVER(PARTITION BY Forename, Surname, DateofBirth order by ID) RN,
Forename,
Surname,
DateofBirth
from table_name) x
where x.rn>1
answered Nov 15 '18 at 11:07
irakliG.irakliG.
1668
Perfect. This worked for me. Thank You
– edcoder
Nov 15 '18 at 11:17
add a comment |
you can use analytical function to achieve your goal like this:
select * from (
select ROW_NUMBER() OVER(PARTITION BY Forename, Surname, DateofBirth order by ID) RN,
Forename,
Surname,
DateofBirth
from table_name) x
where x.rn>1
answered Nov 15 '18 at 11:07
irakliG.irakliG.
1668
Perfect. This worked for me. Thank You
– edcoder
Nov 15 '18 at 11:17
add a comment |
you can use analytical function to achieve your goal like this:
select * from (
select ROW_NUMBER() OVER(PARTITION BY Forename, Surname, DateofBirth order by ID) RN,
Forename,
Surname,
DateofBirth
from table_name) x
where x.rn>1
answered Nov 15 '18 at 11:07
irakliG.irakliG.
1668
you can use analytical function to achieve your goal like this:
select * from (
select ROW_NUMBER() OVER(PARTITION BY Forename, Surname, DateofBirth order by ID) RN,
Forename,
Surname,
DateofBirth
from table_name) x
where x.rn>1
answered Nov 15 '18 at 11:07
irakliG.irakliG.
1668
answered Nov 15 '18 at 11:07
irakliG.irakliG.
1668
answered Nov 15 '18 at 11:07
irakliG.irakliG.
1668
answered Nov 15 '18 at 11:07
irakliG.irakliG.
1668
1668
Perfect. This worked for me. Thank You
– edcoder
Nov 15 '18 at 11:17
add a comment |
Perfect. This worked for me. Thank You
– edcoder
Nov 15 '18 at 11:17
Perfect. This worked for me. Thank You
– edcoder
Nov 15 '18 at 11:17
Perfect. This worked for me. Thank You
– edcoder
Nov 15 '18 at 11:17
add a comment |
use corelated subquery
select t.* from table_name t where
exists ( select 1 from table_name t1 where t1.DateofBirth=t.DateofBirth and t1.Forename=t.Forename
and t1.surname=t.surname
group by t1.DateofBirth,t1.Forename,t1.Surname
having count(*)>1 )
answered Nov 15 '18 at 11:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
add a comment |
use corelated subquery
select t.* from table_name t where
exists ( select 1 from table_name t1 where t1.DateofBirth=t.DateofBirth and t1.Forename=t.Forename
and t1.surname=t.surname
group by t1.DateofBirth,t1.Forename,t1.Surname
having count(*)>1 )
answered Nov 15 '18 at 11:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
add a comment |
use corelated subquery
select t.* from table_name t where
exists ( select 1 from table_name t1 where t1.DateofBirth=t.DateofBirth and t1.Forename=t.Forename
and t1.surname=t.surname
group by t1.DateofBirth,t1.Forename,t1.Surname
having count(*)>1 )
answered Nov 15 '18 at 11:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
use corelated subquery
select t.* from table_name t where
exists ( select 1 from table_name t1 where t1.DateofBirth=t.DateofBirth and t1.Forename=t.Forename
and t1.surname=t.surname
group by t1.DateofBirth,t1.Forename,t1.Surname
having count(*)>1 )
answered Nov 15 '18 at 11:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
answered Nov 15 '18 at 11:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
answered Nov 15 '18 at 11:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
answered Nov 15 '18 at 11:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
16k21033
add a comment |
add a comment |
You can use exists :
select t.*
from table t
where exists (select 1
from table t1
where t1.Forename = t.Forename and t1.Surname = t.Surname and
t1.DateofBirth = t.DateofBirth and t1.id <> t.id
)
answered Nov 15 '18 at 11:03
Yogesh SharmaYogesh Sharma
32.8k51438
add a comment |
You can use exists :
select t.*
from table t
where exists (select 1
from table t1
where t1.Forename = t.Forename and t1.Surname = t.Surname and
t1.DateofBirth = t.DateofBirth and t1.id <> t.id
)
answered Nov 15 '18 at 11:03
Yogesh SharmaYogesh Sharma
32.8k51438
add a comment |
You can use exists :
select t.*
from table t
where exists (select 1
from table t1
where t1.Forename = t.Forename and t1.Surname = t.Surname and
t1.DateofBirth = t.DateofBirth and t1.id <> t.id
)
answered Nov 15 '18 at 11:03
Yogesh SharmaYogesh Sharma
32.8k51438
You can use exists :
select t.*
from table t
where exists (select 1
from table t1
where t1.Forename = t.Forename and t1.Surname = t.Surname and
t1.DateofBirth = t.DateofBirth and t1.id <> t.id
)
answered Nov 15 '18 at 11:03
Yogesh SharmaYogesh Sharma
32.8k51438
answered Nov 15 '18 at 11:03
Yogesh SharmaYogesh Sharma
32.8k51438
answered Nov 15 '18 at 11:03
Yogesh SharmaYogesh Sharma
32.8k51438
answered Nov 15 '18 at 11:03
Yogesh SharmaYogesh Sharma
32.8k51438
32.8k51438
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%2f53317941%2fmssql-search-for-duplicates-in-columns-based-on-criteria%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
