Remove Duplicate Row in Sql Query

Method 1:

-- Create Sample Table

DECLARE @table TABLE (data VARCHAR(20))

-- Insert Some Data

INSERT INTO @table VALUES ('not duplicate row')

INSERT INTO @table VALUES ('duplicate row')INSERT INTO @table VALUES ('duplicate row')

-- Find out Duplicate rows in table:

SELECT  data, COUNT(data) nrFROM    @tableGROUP BY dataHAVING  COUNT(data) > 1

-- Remove Duplicate rows from table

SET NOCOUNT ONSET ROWCOUNT 1WHILE 1 = 1   BEGIN      DELETE   FROM @table
      WHERE    data IN (SELECT  data                               FROM    @table
                               GROUP BY data                               HAVING  COUNT(*) > 1)      IF @@Rowcount = 0          BREAK ;   ENDSET ROWCOUNT 0

-- See Out put after remove duplicate record from table.

SELECT * FROM  @table

Method 2:

DECLARE  @tempData TABLE (data VARCHAR(20))

INSERT INTO @tempData VALUES ('not duplicate row')

INSERT INTO @tempData VALUES ('duplicate row')

INSERT INTO @tempData VALUES ('duplicate row')

INSERT INTO @tempData VALUES ('second duplicate row')

INSERT INTO @tempData VALUES ('second duplicate row')

;

WITH    numbered

          AS ( SELECT   data

                      , row_number() OVER ( PARTITION BY data ORDER BY data ) AS nr

               FROM     @tempData

             )

    SELECT  data

    FROM    numbered

    WHERE   nr > 1