Bhaskar Shukla

• Apr 8th, 2006

 

How to find out duplicate records in sql server

Hanif

• Apr 12th, 2006

 

we have to use the group by with having command to get the duplicate values. this query shall show the result of only the users have duplicate values in the employee table.

Syntex:
Select columnName From Table_name
Group By columnName
Having count (*) > 1

Example:
SELECT  UserID FROM employee
GROUP BY userid
HAVING count( * ) > 1

kalyan

• May 19th, 2006

 

in single line query

Fazlur Rahiman

• Jul 4th, 2006

 

Select Count(*)[No_of_Duplications],UnitPrice From Products
Group by UnitPrice
Having count(UnitPrice) > 1

Anon

• Jul 13th, 2006

 

Select columnName From Table_name Group By columnName Having count (*)> 1

Raj Sekhar

• Aug 17th, 2006

 

Select max(empid),max(empname),salary from emp group by salary having count(salary) >1

srilakshmi.b

 Profile Answers by srilakshmi.b 

• May 30th, 2008

 

select * from table_name group by Columns_names having count(*) >1
-- this gives the duplicate records in the table

ushalakshmi

 Profile Answers by ushalakshmi Questions by ushalakshmi

• Jun 17th, 2008

 

By using following query we wil get the duplicate records

SELECT columnName FROM TableName
Group By columnName
Having Count(*)>1

sunil143

 Profile Answers by sunil143 

• Aug 30th, 2008

 

Select Columnname from Tablename Groupby Columnname Having Count*>1

kalpantpatil

 Profile Answers by kalpantpatil Questions by kalpantpatil

• Apr 27th, 2011

 

Hi,
Here I have found the better ans.
Query :select Designation from employee group by Designation having COUNT(Designation)>1

Explanation : Group by clause specifies operation on result set.Having clause specifies operation on Group by clause.Having clause is similar to where clause excepting we can use aggregate functions with having clause.

e.g COUNT() which counts no. of appearance of record specified in COUNT function.Above query finds "Designation" that appear more than 1

Ramakant Sahoo

• Dec 23rd, 2011

 

Code
SELECT EMAIL, COUNT(*)  "REPETED EMAIL" FROM EMP GROUP BY EMAIL; 

nandu

• Apr 26th, 2012

 

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC