SQLTeam.com | Weblogs | Forums

SQL To Remove Duplicate


#1

Hello,

I have the following data

Customer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)

If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008


#2

Are you generating the row number? If not, then use ROW_NUMBER() to generate a new row number...something like:

SELECT ...
     , ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY SalesOrder DESC) As NewRowNum
  FROM yourTable

Using the above in a CTE will then allow you do to this:

  WITH dups
    AS (
SELECT ...
     , ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY SalesOrder DESC) As NewRowNum
  FROM yourTable
       )
DELETE FROM dups
 WHERE NewRowNum > 1;

If you are already generating the row number - add the DESC to reverse the order and you can then use that in the same way.


#3

There are various command you can try to remove duplicate row. Have a look here you get a good one:
http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server