CodePudding user response:

Don't damage the original table data is what mean? Or you just check after heavy data

After the grammar check is to heavy data

 SELECT * FROM 
(SELECT ROW_NUMBER () OVER (PARTITION BY ProId order BY ProId) AS No, * From Table) A 
WHERE A.N o=1 

CodePudding user response:

 USE tempdb for 
GO 
IF OBJECT_ID (' t ') IS NOT NULL 
DROP TABLE t 
GO 
CREATE TABLE (t 
ProID VARCHAR (10), 
ProName NVARCHAR (10), 
Price a DECIMAL (8, 2), 
Stocks INT, 
DepartNo VARCHAR (10) 
) 
GO 
SET NOCOUNT ON 
INSERT INTO t VALUES (' 002 ', 'air conditioner', 1500, 5, '01); 
INSERT INTO t VALUES (' 003 ', 'washing machine', 1200, 6, '01) 
INSERT INTO t VALUES (' 004 ', 'fridge, 2000, 4,' 01) 
INSERT INTO t VALUES (' 008 ', 'clothes', 120, 8,' 3 ') 
INSERT INTO t VALUES (' 002 ', 'air conditioner', 1500, 5, '01); 
INSERT INTO t VALUES (' 003 ', 'washing machine', 1200, 6, '01) 
INSERT INTO t VALUES (' 004 ', 'fridge, 2000, 4,' 01) 
INSERT INTO t VALUES (' 008 ', 'clothes', 120, 8,' 3 ') 
GO 
-- -- -- -- -- -- -- -- -- -- -- -- the above table and test data for test -- -- -- -- -- -- -- -- -- -- 

Delete data - 1. 
; WITH cte AS (
The SELECT ROW_NUMBER () OVER (PARTITION BY ProID ORDER BY 1) (SELECT) AS rids, * 
The FROM t 
) 
The DELETE FROM cte WHERE rid> 1 

- 2. Verify 
SELECT * FROM t 
/* 
ProID ProName Price Stocks DepartNo 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
002 air conditioning 1500.00 5 01 
004 refrigerator 2000.00 4 01 
003 washing machine 1200.00 6 01 
008 sportswear 120.00 8 03 
*/