How to Find out Unique and Duplicate Data in a Table
If you want to check the data in a table is unique or duplicate.
In Oracle and Ms SQL Server the standard Having clause for SELECT Statement with built in count function which identify the unique data or duplicate data in a table.
SELECT * From EMP
|
Idx
|
FirstName
|
LastName
|
Salary
|
|
1
|
Frank
|
Xylo
|
5000
|
|
2
|
Smith
|
Fay
|
10000
|
|
3
|
Smith
|
Fay
|
15000
|
|
4
|
John
|
Lay
|
12000
|
|
5
|
John
|
Fay
|
14000
|
|
6
|
Ebrahim
|
John
|
9000
|
|
7
|
Frank
|
Xylo
|
11000
|
|
8
|
Frank
|
Xylo
|
7000
|
This query finds the unique data in a table
SELECT Lastname, count(*)
SELECT Lastname, count(*)
From EMP
Where lastname = ‘John’
Group By Lastname
Having count(*) = 1;
|
LasteName
|
Count(*)
|
|
John
|
1
|
This query finds the duplicate data in a table
SELECT Lastname, count(*)
From EMP
Where lastname = ‘Fay’
Group By Lastname
Having count(*) > 1;
|
LasteName
|
Count(*)
|
|
Fay
|
3
|
