How to retrieve only duplicate records in database table?

Showing Answers 1 - 4 of 4 Answers

Atlantis67

  • May 28th, 2015
 

Im sure there are several ways to do it.
Why not using a group by query ?
suppose you want to list duplicate names and phone numbers in a customer table :

Select
Name,
Phone,
Count(*) as Nb
From
Customer
Group By
Name,
Phone
Where
Nb>1

  Was this answer useful?  Yes

Ashwinee Kumar

  • Jun 1st, 2015
 

Code
  1. WITH T AS

  2. (

  3. SELECT RN=Row_Number() over(partition BY ColumnName1,.. ORDER BY ColumnName1,...) , * FROM TableName

  4. ) SELECT * FROM T WHERE RN>1

  Was this answer useful?  Yes

ashbittu

  • Jun 1st, 2015
 

Code
  1. WITH T AS

  2.         (

  3.                 SELECT RN=ROW_NUMBER() Over(PARTITION BY Column1, ... ORDER BY Column1,...),

  4.                 * FROM TableName

  5.         )

  6.                 SELECT * FROM T WHERE RN>1

  7.  

  Was this answer useful?  Yes

Sharath

  • Jun 8th, 2015
 

Select ename ,count(ename) from emp group by ename

  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