Remove Duplicate Rows in SQL

Remove Duplicate Rows in SQL

–>CREATE TABLE [Employee](
[ID] INT NOT NULL,
[Email] [varchar](50) NOT NULL
);

–drop table dbo.Employee

INSERT INTO [dbo].[Employee] VALUES (1,’test1@gmail.com’);
INSERT INTO [dbo].[Employee] VALUES (2,’test1@gmail.com’);
INSERT INTO [dbo].[Employee] VALUES (3,’test2@gmail.com’);
INSERT INTO [dbo].[Employee] VALUES (4,’test2@gmail.com’);
INSERT INTO [dbo].[Employee] VALUES (5,’test3@gmail.com’);
INSERT INTO [dbo].[Employee] VALUES (6,’test1@gmail.com’);
INSERT INTO [dbo].[Employee] VALUES (7,’test3@gmail.com’);
GO

—-SELECT MAX(ID),EMAIL,ROW_NUMBER() OVER (Order By EMAIL ASC) AS ‘ROW’
–FROM EMPLOYEE
–GROUP BY EMAIL

–before deleting

SELECT * FROM EMPLOYEE
GO

WITH T1
AS
(
SELECT ID FROM DBO.EMPLOYEE
EXCEPT
SELECT MAX(ID) AS ID
FROM DBO.EMPLOYEE
GROUP BY Email
HAVING COUNT(Email) > 1
)
DELETE FROM dbo.Employee
WHERE ID IN (SELECT ID FROM T1)
GO

–Check after deleting

SELECT * FROM DBO.EMPLOYEE
GO

–droping table

DROP TABLE EMPLOYEE
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s