CODEWITHSUNDEEP
sql-server-2008
coure2011
coure2011

Reputation: 42474

delete all rows where column_id is xyz except one randomly selected

I have a record in table something like this

ID   Name   Value
1    abc    123
2    abc    152
3    cde    574
4    def    153
5    abc    777

How to delete row from the above column based on this algorithm,

We have same name for 3 rows (ID: 1,2,5). Delete all these rows except any one selected randomly. Same applied for other Names

Possible using sql or T-SQL?

Upvotes: 0

Views: 93

Answers (1)

i-one
i-one

Reputation: 5120

;with d as (
    select *, rowNum = row_number() over (partition by Name order by checksum(newid()))
    from TableName
)
delete d
where rowNum > 1

Upvotes: 1

Related Questions