Duplicate row Remove

• anant.jain1974

  Grasshopper

  Points: 11

  More actions

  August 6, 2008 at 9:31 am

  #192142

  Dear Sir

  I hv a table with col1,col2,col3,col4,col5 & i want to remove the all those rows where col1,col2 & col3 having duplicate. thx in adv.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  August 6, 2008 at 9:45 am

  #853934

  ;with CTE (Row, Col1, Col2, Col3) as

  (select row_number() over (partition by col1, col2, col3 order by col1),

  col1, col2, col3

  from dbo.Table)

  delete from cte

  where row > 1

  That should do it.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon