It is very easy to delete duplicate record from table in sql server. SQL Server always stores each tupple (Row) as unique into the table.
To see duplicate record, we can use the count function with group by clause with having in the condition.
To delete the record to Max function with NOT IN keyword.
Just execute and see how it work to delete duplicate record into table.
USE tempdb
GO
CREATE TABLE Jainendra_TestTable (My_ID INT, Rank_Col VARCHAR(50))
Go
INSERT INTO Jainendra_TestTable (My_ID, Rank_Col)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'No Rank'
UNION ALL
SELECT 3, 'Second'
UNION ALL
SELECT 4, 'Second'
UNION ALL
SELECT 5, 'Second'
UNION ALL
SELECT 6, 'Third'
UNION ALL
SELECT 7, 'Five'
UNION ALL
SELECT 8, 'Second'
UNION ALL
SELECT 9, 'Five'
UNION ALL
SELECT 10, 'Nine'
UNION ALL
SELECT 11, 'Third'
GO
-- See the inserted data in create table
SELECT *
FROM Jainendra_TestTable
GO
-- Now below query is detecting duplicate records into table
SELECT Rank_Col, COUNT(*) TotalCount
FROM Jainendra_TestTable GROUP BY Rank_Col
HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC
GO
-- Now below query is deleting the duplicate record into table
DELETE FROM Jainendra_TestTable
WHERE My_ID NOT IN
( SELECT MAX(My_ID) FROM Jainendra_TestTable GROUP BY Rank_Col)
GO
-- Selecting Data
SELECT *
FROM Jainendra_TestTable
GO
DROP TABLE Jainendra_TestTable
GO
If it is useful than please like and share it to other SQL Server learners
