How to find out duplicate records in sql server?

Editorial / Best Answer

Answered by: 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

Showing Answers 1 - 12 of 12 Answers

Bhaskar Shukla

  • Apr 8th, 2006
 

How to find out duplicate records in sql server

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Fazlur Rahiman

  • Jul 4th, 2006
 

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

  Was this answer useful?  Yes

Anon

  • Jul 13th, 2006
 

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

  Was this answer useful?  Yes

Raj Sekhar

  • Aug 17th, 2006
 

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Ramakant Sahoo

  • Dec 23rd, 2011
 

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

  Was this answer useful?  Yes

nandu

  • Apr 26th, 2012
 

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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions