Friday, November 12, 2010

Deleting Duplicate Records In SQL Server using ROW_NUMBER() and PARTITION BY

--Drop table [dbo].[SalesHistory]
CREATE TABLE [dbo].[SalesHistory] (
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL )
GO
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
SELECT 'Computer','1919-03-18 00:00:00.000',1008.00
UNION ALL SELECT 'BigScreen','1927-03-18 00:00:00.000',91.00
UNION ALL SELECT 'PoolTable','1927-04-01 00:00:00.000',139.00
UNION ALL SELECT 'Computer','1919-03-18 00:00:00.000',1008.00
UNION ALL SELECT 'BigScreen','1927-03-25 00:00:00.000',92.00
UNION ALL SELECT 'PoolTable','1927-03-25 00:00:00.000',108.00
UNION ALL SELECT 'Computer','1919-04-01 00:00:00.000',150.00
UNION ALL SELECT 'BigScreen','1927-04-01 00:00:00.000', 123.00
UNION ALL SELECT 'PoolTable','1927-04-01 00:00:00.000', 139.00
UNION ALL SELECT 'Computer','1919-04-08 00:00:00.000', 168.00
select * from SalesHistory


delete from a from
(
SELECT Product, SaleDate, SalePrice,
DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) Ranking
FROM SalesHistory
) a where Ranking>1

---- Same can be done with ROW_Number Also

delete from a from
(
SELECT Product, SaleDate, SalePrice,
ROW_Number() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) Ranking
FROM SalesHistory
) a where Ranking>1

No comments:

Post a Comment