One occurrence of a word only - Delete repeated rows

• monzulu

  Mr or Mrs. 500

  Points: 528

  More actions

  October 29, 2010 at 4:04 am

  #230480

  hi guys any advice how to do this please? i want to delete the rows that are repeated (not by having a select distinct...) i just want 1 occurrence of a word. thanks for any help.

  from table like this:

  words

  ------

  apple

  apple

  grapes

  banana

  grapes

  blueberry

  grapes

  blueberry

  apple

  plum

  plum

  blueberry

  banana

  blueberry

  into this:

  words

  --------

  apple

  grapes

  banana

  blueberry

  plum

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 29, 2010 at 4:16 am

  #1244041

  There are loads of threads on how to do this, if you'd care to search for them. You need to do something like this (not tested):

  WITH MyWords AS (

  SELECT

  ROW_NUMBER() OVER (PARTITION BY Word ORDER BY Word) AS seq

  ,Word

  FROM

  Words

  )

  DELETE FROM

  MyWords

  WHERE

  seq > 1

  John

• sharath.chalamgari

  SSCertifiable

  Points: 5680

  More actions

  October 29, 2010 at 4:53 am

  #1244047

  Create table Fruits

  (Fruit varchar(10))

  insert into Fruits

  select 'apple' Union all

  select 'apple' Union all

  select 'grapes' Union all

  select 'banana' Union all

  select 'grapes' Union all

  select 'blueberry' Union all

  select 'grapes' Union all

  select 'blueberry' Union all

  select 'apple' Union all

  select 'plum' Union all

  select 'plum' Union all

  select 'blueberry' Union all

  select 'banana' Union all

  select 'blueberry' Union all

  with cte

  As

  (

  select Row_number() over ( partition by Fruit order by Fruit desc) as id,Fruit

  From Fruits

  )

  Delete From cte where id >1

  select * from Fruits