DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to delete duplicates in a table?

The following sample SQL Server T-SQL script demonstrates the deletion of duplicates on columnA using a derived table (b) w:

DELETE a

FROM   TableX a

       INNER JOIN (SELECT   columnA,

                            minID = min(TableXID)

                   FROM     TableX

                   GROUP BY columnA) b

         ON a.columnA = b.columnA

            AND a.TableXID > b.minID

------------

 

------------

-- SELECT UNIQUE SalesOrderID-s with GROUP BY

------------

SELECT SalesOrderID,

  CNT=COUNT(*)

  FROM AdventureWorks2008.Sales.SalesOrderDetail

  WHERE ProductID > 799

  GROUP BY SalesOrderID

  HAVING COUNT(*)=1

------------

Starting with SQL Server 2005, CTE can be used to delete duplicates.

-- T-SQL using ranking function ROW_NUMBER to delete duplicates

USE tempdb;

SELECT EmployeeId = CONVERT(INT,EmployeeID), ManagerID

INTO Employee

FROM AdventureWorks.HumanResources.Employee

GO

 

INSERT Employee VALUES (100, 109)

INSERT Employee VALUES (150, 109)

INSERT Employee VALUES (190, 109)

GO

;WITH CTE AS

  (SELECT rn=ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY ManagerID), *

   FROM Employee)

DELETE CTE

WHERE rn > 1

GO

-- (3 row(s) affected)

 

DROP TABLE Employee

 

------------

-- SQL eliminate duplicates with DISTINCT

------------

USE tempdb;

GO

-- Create test data

SELECT ProductID=CONVERT(int, ProductID), ProductName=Name,

       ListPrice, Color

INTO Product

FROM AdventureWorks2008.Production.Product

GO

-- (504 row(s) affected)

-- Create duplicates

INSERT Product SELECT * FROM Product

GO

-- (504 row(s) affected)

 

-- Eliminate duplicates with DISTINCT

SELECT DISTINCT * INTO newProduct FROM Product

-- (504 row(s) affected)

SELECT COUNT(*) FROM Product    -- 1008

SELECT COUNT(*) FROM newProduct -- 504

GO

DROP TABLE tempdb.dbo.Product

DROP TABLE tempdb.dbo.newProduct

GO

------------

 

------------

-- SELECT UNIQUE SalesOrderID-s

------------

WITH CTE  AS (SELECT SalesOrderID,

  CNT=COUNT(*) OVER(PARTITION BY SalesOrderID)

  FROM AdventureWorks2008.Sales.SalesOrderDetail

  WHERE ProductID > 799)

SELECT SalesOrderID FROM CTE WHERE CNT=1

------------

 

Related KB article: How to remove duplicate rows from a table in SQL Server

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE