FIND DUPLICATE ROWS IN A TABLE IN SQL SERVER 2005

CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EMPNAME VARCHAR(100),
SALARY DECIMAL
)

SELECT * FROM EMPLOYEE

INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 80000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 40000 )
INSERT INTO EMPLOYEE VALUES ( ‘Rahul’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Gauri’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Gauri’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Rahul’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Deepa’ , 100000 )

–Query to find out the rows with duplicate names in the above table

–Ouery will give u the name
SELECT EmpName FROM EMPLOYEE GROUP BY EmpName HAVING COUNT(EmpName) > 1

–Query if you all the details
SELECT id , EmpName , Salary FROM Employee WHERE EmpName IN
(SELECT EmpName FROM EMPLOYEE GROUP BY EmpName HAVING COUNT(EmpName) > 1)

Pawan Kumar
Pawankkmr@hotmail.com

Advertisements