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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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