CODEWITHSUNDEEP
oracle-database
fatherazrael
fatherazrael

Reputation: 5977

Oracle SQL: How to remove duplicate employee Ids from results?

I need to write query to remove duplicate employee ids among 10,000 results 

EmpID   name

 1       x
 1       x
 2       y
 2       y
 3       z
 4       A

The result should be only:

EmpID   name

 3       z
 4       A

 Select * from EMPLOYEE where ?

How can I do this?

Upvotes: 0

Views: 202

Answers (3)

Popeye
Popeye

Reputation: 35900

I would really ask you to use other than the GROUP BY method as you will be able to fetch other fields of the EMPLOYEE table along with EMPID and NAME.

Using analytical function

SELECT * FROM 
(SELECT T.*, COUNT(1) OVER (PARTITION BY EMPID) AS CNT
FROM EMPLOYEE) 
WHERE CNT = 1;

Using NOT EXISTS

SELECT * FROM EMPLOYEE T
WHERE NOT EXISTS 
(SELECT 1 FROM EMPLOYEE TIN 
WHERE TIN.ID = T.ID AND TIN.ROWID <> T.ROWID);

Cheers!!

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need aggregation : 

select e.empid, e.name
from employee e 
group by e.empid, e.name
having count(*) = 1;

Upvotes: 4

Fahmi
Fahmi

Reputation: 37473

You can try below -

Select empid,name from EMPLOYEE
group by empid,name
having count(*)=1

Upvotes: 1

Related Questions