Duplicate records based on specific columns list

Question

Post reply

Duplicate records based on specific columns list

sonyMadhu

SSC Rookie

Points: 26
More actions
October 10, 2011 at 6:08 am

#244348

Hi All,
I have a requirement like in my sample table
there are empid, firstname, lastname, salary, date joined.
Now in Sample table i want only the list of all duplicate records
where firstname and lastname is same.(i.e based on column specification).
Resultant Select query should contain columns,
empid, firstname, lastname, salary, date joined.
How can i achieve this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

How to post data/code on a forum to get the best help

http://www.sqlservercentral.com/articles/Best+Practices/61537/

sonyMadhu SSC Rookie Points: 26 More actions · Answer 2

Ha Ha ha:-)

Is this answer to my post or suggestion for how to post query in sqlservercentral.com..

Any way i am expecting answer for my post..

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 3

You may have to follow certain pattern to help you better!!!

Otherwise people wont take effort to create tables and data for you which would be areal example.

Answering your question,

you have to use Group by with having with count greater than 1. then get the empid(considering its unique) and joing the base table to get result set.

Sorry, no time to write a query as long as you are not ready to give the table schema and data.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

sonymadhu (10/10/2011)
Hi All,
I have a requirement like in my sample table
there are empid, firstname, lastname, salary, date joined.
Now in Sample table i want only the list of all duplicate records
where firstname and lastname is same.(i.e based on column specification).
Resultant Select query should contain columns,
empid, firstname, lastname, salary, date joined.
How can i achieve this?

Use COUNT() with OVER(), Partitioning by firstname and lastname. Select the results from a CTE or derived table where the row count per partition is greater than 1.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sonyMadhu SSC Rookie Points: 26 More actions · Answer 5

Hey frnds,

I am new to this site actually, thats the problem..

Just i followed how i post queries in other sites juz did the same here also..

at last i got answer for my post..

thanks to all...

select empid, firstname, lastname, salary, date joined

from sample a

where (select COUNT(*) from sample b where a.firstname=b.firstname and a.lastname=b.lastname)>1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

sonymadhu (10/10/2011)
Hey frnds,
I am new to this site actually, thats the problem..
Just i followed how i post queries in other sites juz did the same here also..
at last i got answer for my post..
thanks to all...
select empid, firstname, lastname, salary, date joined
from sample a
where (select COUNT(*) from sample b where a.firstname=b.firstname and a.lastname=b.lastname)>1

Don't do it like this, you will lose points. Your tutor will be looking for COUNT() with OVER(). Try it, if you have difficulty, post what you've tried so we can correct it for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden