Skip to main content

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



Comments

Popular posts from this blog

send push notification from C# using FCM

        public bool SendPushNotification(string deviceId, string message)         {      //applicationID = "AIzaSyxxxxxxxxxxxxxxxxxxx"     // senderId = "98xxxxxxxxx"             bool isSent = false;             string str = "";             try             {                 WebRequest tRequest = WebRequest.Create("https://fcm.googleapis.com/fcm/send");                 tRequest.Method = "post";                 tRequest.ContentType = "application/json";                 var data = new                 {                     to = deviceId,   ...