--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
Friday, November 12, 2010
Deleting Duplicate Records In SQL Server using ROW_NUMBER() and PARTITION BY
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment