Sql Query

Hi all, I have a sql table and need to write a piece of code to identify duplicates and then delete the duplicates.  Fields are RecordID and PeopleID and I want to identify which records exist where PeopleID exists more than once.

Any help would be greatly appreciated!

Thanks.

JIm
jwebster77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT RecordID, COUNT(PeopleID)
FROM TableName
GROUP BY RecordID
HAVING COUNT(PeopleID)>1

Open in new window

0
Scott PletcherSenior DBACommented:
It's not clear to me what  you want.


Do you want find where the same PeopleID is on different RecordIDs?
SELECT PeopleID
FROM TableName
GROUP BY PeopleID
HAVING COUNT(DISTINCT RecordID) > 0


Do you want find where the same PeopleID appears more than once, regardless of RecordID?
SELECT PeopleID
FROM TableName
GROUP BY PeopleID
HAVING COUNT(*) > 0


Something else?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I've changed the field names. This should be the correct version:
SELECT PeopleID, COUNT(RecordID)
FROM TableName
GROUP BY PeopleID
HAVING COUNT(RecordID)>1

Open in new window

0
Deepak ChauhanSQL Server DBACommented:
You can use CTE to identify the duplicate and delete them

<Table Name> -- replace with your table name.

with cte
as
(
select ROW_NUMBER() over(PARTITION by PeopleID order by RecordID ) RNO, RecordID, PeopleID  
from <TableName>
)
select *  FROM cte  where RNO >1
--delete from CTE where RNO > 1  -- uncomment it when you want to delete and comment the select statement
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jwebster77Author Commented:
Doesn't matter.  RecordId is a auto increment field .  RecordID will never be the same.

Do you want find where the same PeopleID is on different RecordIDs?
SELECT PeopleID
FROM TableName
GROUP BY PeopleID
HAVING COUNT(DISTINCT RecordID) > 0


Do you want find where the same PeopleID appears more than once, regardless of RecordID?
SELECT PeopleID
FROM TableName
GROUP BY PeopleID
HAVING COUNT(*) > 0
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.