ลบข้อมูลเรคคอร์ดที่ซ้ำออกไปให้เหลือเรคคอร์ดเดียวค่ะ

CREATE TABLE #SomOOO_Request_03_2010
(
    YearRange		INT NULL,
    MonthRange		INT NULL,
    StockCode		NVARCHAR(100) NULL,
    Warehouse		NVARCHAR(100) NULL,
    balQty			INT NULL,
    balVal			FLOAT NULL,
    sale			FLOAT NULL
 ) ;
INSERT #SomOOO_Request_03_2010 VALUES
   (2010,5,'107645.0010' ,'SAMPLE' ,0 ,0 ,0),
   (2010,5,'107645.0010' ,'SAMPLE' ,0 ,0 ,0),
   (2010,5,'107646.0010' ,'SAMPLE' ,0 ,0 ,0),
   (2010,5,'107646.0010' ,'SAMPLE' ,0 ,0 ,0),
   (2010,5,'107651.0010' ,'01' ,46 ,0 ,0),
   (2010,5,'107651.0010' ,'01' ,46 ,0 ,23),
   (2010,5,'107653.0010' ,'01' ,1 ,0 ,0),
   (2010,5,'107653.0010' ,'01' ,1 ,0 ,0),
   (2010,5,'107659.0010' ,'EXTRA' ,0 ,0 ,0),
   (2010,5,'107659.0010' ,'EXTRA' ,0 ,0 ,0),
   (2010,5,'107659.0010' ,'EXTRA' ,0 ,0 ,0),
   (2010,5,'107659.0010' ,'EXTRA' ,0 ,0 ,10),
   (2010,5,'107659.0010' ,'EXTRA' ,0 ,0 ,40),   
   (2010,5,'107659.0010' ,'EXTRA' ,0 ,0 ,40);        


SELECT * FROM #SomOOO_Request_03_2010; 

-- FIRST SUBJECT ELIMINATE SALE ZERO REMAIN AT ONCE
WITH AddFakeIDTABLE AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY '{' + StockCode + '}.{'+ Warehouse + '}' ) AS FAKE_ID 
	,'{' + StockCode + '}.{'+ Warehouse + '}'  AS [BindKey]
	,* FROM  #SomOOO_Request_03_2010
)     
-- DELETE FROM JOIN RESULTSET
DELETE FROM AddFakeIDTABLE
FROM
	(
		SELECT [FAKE_ID] ,[BindKey] FROM AddFakeIDTABLE 	
		WHERE [sale] =0		
	) AS A1 (FAKE_ID,[BindKey])
	RIGHT JOIN 
	(
		SELECT MIN([FAKE_ID]) AS [LEAST_ID],[BindKey] FROM AddFakeIDTABLE 	
		WHERE [sale] =0
		GROUP BY [BindKey]
		HAVING COUNT([FAKE_ID])>1
	) AS A2 ([LEAST_ID] ,[BindKey])
	
	ON A1.[BindKey] = A2.[BindKey]
WHERE (AddFakeIDTABLE.[BindKey]=A1.[BindKey])
	AND (AddFakeIDTABLE.[FAKE_ID] <> A2.[LEAST_ID]) AND (A1.[FAKE_ID] <> A2.[LEAST_ID]) 
    AND (SALE=0);


SELECT * FROM #SomOOO_Request_03_2010; 	 

-- FINAL PHASE
WITH AddFakeIDTABLE AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY '{' + StockCode + '}.{'+ Warehouse + '}' ,[sale] ASC) AS FAKE_ID 
	,'{' + StockCode + '}.{'+ Warehouse + '}' AS [BindKey]
	,* FROM  #SomOOO_Request_03_2010
)      
DELETE FROM AddFakeIDTABLE
FROM
	AddFakeIDTABLE
	RIGHT JOIN 
	(
		SELECT MAX([FAKE_ID]) AS [MOST_ID],[BindKey] FROM AddFakeIDTABLE 
		GROUP BY [BindKey]
		HAVING COUNT([FAKE_ID])>1
	) AS A2 ([MOST_ID] ,[BindKey])
	
	ON AddFakeIDTABLE.[BindKey] = A2.[BindKey]
WHERE AddFakeIDTABLE.[FAKE_ID] <> A2.[MOST_ID]	 ;
 
SELECT * FROM #SomOOO_Request_03_2010; 

DROP TABLE #SomOOO_Request_03_2010;