Technical Article

Removing Duplicates

,

Suppose we have a table with duplicate entries as follows:

 

CREATE TABLE T1
(
ProductName varchar(50)
)

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

 

You can also deleted the duplicates using the following approach

DELETE D FROM
    (
    SELECT ProductName, ROW_NUMBER()OVER(ORDER BY ProductName) AS RowNum 
    FROM T1
    )D
    JOIN
    (
    SELECT ProductName, ROW_NUMBER()OVER(ORDER BY ProductName) AS RowNum 
    FROM T1
    )E
on D.ProductName = E.ProductName
AND D.RowNum < E.RowNum    

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.86 (14)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.86 (14)

You rated this post out of 5. Change rating