Saturday, 1 June 2013

delete duplicate records in sql server

                                  In this article we will see how to delete duplicate records by using
Comman table expression : and Row_number() function in sql server.

Example:

step1: Create a table


       CREATE TABLE
#TEMP
     (
               id int
              ,city VARCHAR(50)
              ,sNAME VARCHAR(50)

     )

step2: insert data into table



        INSERT INTO
#TEMP (id,city,SNAME)VALUES(1,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(2,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(3,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(4,'sangli','SANDIPg')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(5,'sangli','SANDIPg')

       INSERT INTO #TEMP (id,city,SNAME)VALUES(6,'kolhapur','SANDIP')

       INSERT INTO #TEMP (id,city,SNAME)VALUES(7,'kolhapur','SANDIP')

       INSERT INTO #TEMP (id,city,SNAME)VALUES(8,'kolhapur','SANDIP')

       INSERT INTO #TEMP (id,city,SNAME)VALUES(9,'mumbai','SANDIP')

        INSERT INTO #TEMP (id,city,SNAME)VALUES(10,'mumbai','SANDIP')


step3:: use below query for delete a duplicate records from your table


      WITH
CTE AS
    (
              select id,city,SNAME, row_number()
              over(partition by SNAME order byid desc)
              duplicate_id from #TEMP
    )
          deleteCTE where duplicate_id>1
          select * from #temp

explaination::

                 I have used a row_number() function for numbering a duplicate records.
for your practice you can try same query for select duplicate cities
In This article I have explain how to delete duplicate records from table by using
row_number() function and comman table expression.
i hope it is helpfull to you ,if it is then give me a comments ,advice ,your questions,and
suggestion

,Thanks

1 comment:

  1. I need query for same scenario with out using windows functions. Thank You in advance..!!!

    ReplyDelete

8451831077