Remove duplicate row

This is a table in SQL Server 2017.
Table Test01 has three columns, ID, main, Associate.

Create table T1 (
ID       Integer,
main      nvarchar(255),
Associate    nvarchar(255));

Column id is a sequence number and does not have duplicates.

The query below returns  197 rows. Means it has duplicates.
Select main, associate, count (*)
 from T1
group by   main, associate
Having count (*) > 1;

Select * from T1 where associate = '1234';   returns 2 rows.

ID   MAIN       Associate
7     130          1234
11   130          1234

Question: Write a delete or sql-to-sql statement that loops through the table T1  and deletes ONLY the duplicate value while keeping one of the rows. Hence the above query Select * from T1 where associate = '1234'; should return only one row.

ID   MAIN       Associate
7     130          1234

or

ID   MAIN       Associate
11   130          1234
 
Thanks.
Kamal AgnihotriAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello KamalAgnihotri

Need to use RANKing function row_number. Please make an attempt to use changed solution

SELECT ID ,  MAIN,  Associate
FROM
(
      Select *, ROW_NUMBER() OVER (PARTITION BY  main, associate ORDER BY ID ASC) rownum
      from T1
)t
WHERE rownum = 1

Vaibhav
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello KamalAgnihotri

Another answer. Please make an attempt to use changed solution.

If Oldest Id is required.

Select main, associate, MIN(Id) ID
from T1
group by   main, associate

If Latest Id is required.

Select main, associate, MAX(Id) Id
from T1
group by   main, associate

Vaibhav
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello KamalAgnihotri

Another Answer. Please make an attempt to use changed solution

If Oldest Id is required.

SELECT T1.main, T1.associate, T3.Id FROM
(
      Select main, associate
      from T1
      group by   main, associate
) AS T1
CROSS APPLY ( SELECT TOP 1 Id FROM T1 AS T2 WHERE T1.main = T2.main AND T1.associate = T2.associate ORDER BY Id ) as T3

If Latest Id is required.

SELECT T1.main, T1.associate, T3.Id FROM
(
      Select main, associate
      from T1
      group by   main, associate
) AS T1
CROSS APPLY ( SELECT TOP 1 Id FROM T1 AS T2 WHERE T1.main = T2.main AND T1.associate = T2.associate ORDER BY Id DESC ) as T3

Vaibhav
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ganesh GuruduSenior ConsultantCommented:
There are many way you can delete duplicate records.
But this could be the simple and fastest way. and this will keep latest record based on sequence number.
DELETE FROM T1 WHERE ID NOT IN (SELECT MAX(ID)      FROM T1 GROUP BY main,Associate)

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
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Author

The above solutions I provided are for selecting data. If you want to permanent delete then make an attempt to use below changed solution.
/*Beware - Take backup of your Table using, SELECT * INTO NewTable FROM T1. Then use the delete statement.*/

;WITH CTE_del AS
(
      Select *, ROW_NUMBER() OVER (PARTITION BY  main, associate ORDER BY ID ASC) rownum
      from T1
)
DELETE FROM CTE_del  WHERE rownum > 1

Vaibhav
Mark WillsTopic AdvisorCommented:
lots of suggestions, kinda difficult to follow...

think it is easiest to  keep 1. ie remove any rows that doesnt match the wanted 1 row, that way you handle triplicates etc.

so to keep lowest ID (corresponding to earliest row) then sort by ID ASC, arguably the later one is the duplicate entry and should be removed. If not, then sort by ID desc to keep the more recent addition (the higher the ID the more recently it was being added)

So
;with Dupes_CTE as
(select row_number() over (partition by main, associate order by id) as RN, main, associate, ID from T1)
 select * from Dupes_CTE 
 

Open in new window

Once satisfied with your query, then drill down a bit further to show the delete candidates
;with Dupes_CTE as
(select row_number() over (partition by main, associate order by id) as RN, main, associate, ID from T1)
 select * from Dupes_CTE where RN > 1
 

Open in new window

Then, when absolutely sure (and backups are performed) you can do the delete instead of the select
;with Dupes_CTE as
(select row_number() over (partition by main, associate order by id) as RN, main, associate, ID from T1)
 delete Dupes_CTE where RN > 1

Open in new window


Looks a bit like an assignment / homework question... Is that correct ?
All your other questions have been Oracle, so made me wonder a little...
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Mark,

Did you get a chance to look at my previous answers/comments ?

Cheers,
Vaibhav
Mark WillsTopic AdvisorCommented:
Hi pawan, no mate, saw three or four different suggestions in fairly quick time with variations and just thought it was going to be another variation.

Not using code blocks makes it a different reading experience as well...

Cheers,
Mark Wills
Vaibhav GoelMSBI , SQL ConsultantCommented:
My Name is Vaibhav.
Mark WillsTopic AdvisorCommented:
Sorry about that Vaibhav, such very similar styles. Freudian slip.
Kamal AgnihotriAuthor Commented:
Thanks. That worked.
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
SQL

From novice to tech pro — start learning today.