duplicate rows

• harri.reddy

  SSCommitted

  Points: 1957

  More actions

  November 11, 2012 at 5:04 pm

  #265286

  hi

  my table has following data

  productno productname des quantity

  1 borin 4x-mal 2

  1 borin 5x-cal 3

  2 hypoid 4-5cal 4

  2 hypoid 4-5cal 4

  here,i want to delete rows which has productno and productname same,it doesnt matter des and quantity is same or not.

  i just want 1 row ,with combination of productno and productname

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  November 12, 2012 at 5:16 am

  #1558112

  http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows

  Using Google for a bit probably won't kill you.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• MMartin1

  One Orange Chip

  Points: 27799

  More actions

  November 16, 2012 at 1:46 pm

  #1559939

  If I understood correctly >

  create table #t1 (productNo tinyint, productname varchar(10),

  Des varchar(50), quantity tinyint)

  Insert into #t1

  select 1, 'borin', '4x-mal', 2 union all

  select 1, 'borin', '5x-cal', 3 union all

  select 2, 'hypoid', '4-5cal', 4 union all

  select 2, 'hypoid', '4-5cal', 4

  ---

  ;with cte as (

  select row_Number() over (partition by productNo, ProductName

  order by quantity desc /* arbitrary */) AS COLUMN1,

  *

  FROM #T1)

  DELETE FROM CTE WHERE COLUMN1>1

  select * from #t1

  drop table #t1

  ----------------------------------------------------