quote:

---

Originally posted by hspatil31

Dear All,

In my table I have following current data. I want to delete duplicate records from table.

I am trying following SQL statement, So I am getting
desired output which is orange color in current data. I want to keep the desired output records and other all should be deleted.
But complection is that in current data values in C5 and C6 are different.

select C1,C2,C3,C4,COUNT(*) from table_Delete
group by C1,C2,C3,C4
having COUNT(*) >= 1
ORDER BY COUNT(*) DESC

Can anyone please help me how to resolve this issue ?

Current Data:

C1 C2 C3 C4 C5 C6
201 203 224 325E 0000 0999

201 203 224 325E 0000 9999
201 203 224 325E 1000 1999
201 203 224 325E 3000 3999
201 203 224 325E 4000 4999
201 203 224 325E 5000 5999
201 203 224 325E 6000 6999
201 203 224 325E 7000 7999
201 203 224 325E 8000 8999
201 203 224 325E 9000 9999

201 203 224 389C 2000 2999 1
Desired Output:

C1 C2 C3 C4
201 203 224 325E 10
201 203 224 389C 1

Thanks and Regard's
Harish Patil

---

select C1,C2,C3,C4,COUNT(*) from table_Delete
group by C1,C2,C3,C4

;WITH cte AS
(
	SELECT *,
	ROW_NUMBER() OVER (PARTITION BY c1,c2,c3,c4 ORDER BY c6) AS RN
	FROM YourTable
)
DELETE FROM cte WHERE RN > 1;