SQL Server 2008: delete duplicate rows -

i have duplicate rows in table, how can delete them based on single column's value?

eg

uniqueid, col2, col3 ... 1, john, simpson 2, sally, roberts 1, johnny, simpson  delete duplicate uniqueids   1, john, simpson 2, sally, roberts 

you can delete cte:

with cte (select *,row_number() over(partition uniqueid order col2)'rowrank'              table) delete cte  rowrank > 1 

the row_number() function assigns number each row. partition by used start numbering on each item in group, in case each value of uniqueid start numbering @ 1 , go there. order by determines order numbers go in. since each uniqueid gets numbered starting @ 1, record row_number() greater 1 has duplicate uniqueid

to understanding of how row_number() function works, try out:

select *,row_number() over(partition uniqueid order col2)'rowrank' table order uniqueid 

you can adjust logic of row_number() function adjust record you'll keep or remove.

for instance, perhaps you'd in multiple steps, first deleting records same last name different first names, add last name partition by:

with cte (select *,row_number() over(partition uniqueid, col3 order col2)'rowrank'              table) delete cte  rowrank > 1