web analytics

Removing Duplicates through SQL Query

Reading Time: 1 minute

Suppose we have a table in SQL Server, that has duplicates in it.

CREATE TABLE T1
(
ProductName varchar(50)
)

INSERT INTO T1
VALUES
(‘Computer’),
(‘Computer’),
(‘Printer’),
(‘Printer’),
(‘Printer’),
(‘Scanner’),
(‘Scanner’),
(‘Scanner’),
(‘Scanner’),
(‘Camera’),
(‘Flash Drive’),
(‘Flash Drive’)

now use the following query, that will remove duplicates in the temporary table that we just created:

DELETE D FROM
(SELECT ProductName,ROW_NUMBER() OVER (ORDER BY ProductName ASC) AS ROWID  FROM T1 ) D,
(SELECT ProductName,ROW_NUMBER() OVER (ORDER BY ProductName ASC) AS ROWID  FROM T1 ) E
where D.ProductName = E.ProductName AND D.ROWID < E.ROWID

One thought on “Removing Duplicates through SQL Query

Leave a Reply

%d bloggers like this: