Saturday, 1 June 2013

select duplicate records in sql server

                                  In this article we will see how to select 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 select 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
    )
             
          select * CTE where duplicate_id>1


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 select 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

No comments:

Post a Comment

8451831077