How to identify duplicates in SQL

 Step1.

There are multiple ways which can be used to identify the duplicate records .

By using row_number function is the best and efficient way.

Example:

Table 1

ID  Name  LOC   Amount

1    GHJ      UA     200

1    GHJ      UA     200

2     MKL     IN      300

3     PER      Aus     400

Identify duplicate records by using below query:

Query:

Select ID,Name,Loc,count(*) from EMP

Group by ID,Name,Loc Having count(*)>1

Query:

With Duplicate

as

select ID,name,Loc,Row_number() over (partition by ID,Name,LOC order by ID)As dup

delete from Duplicate where dup>1