CODEWITHSUNDEEP
sqloracle11gduplicates
user1751356
user1751356

Reputation: 615

oracle duplicate rows based on a single column

How can I find out duplicate rows based on a single column. I have a table in oracle which has data as given below and it has duplicates. I'm trying to select and view all rows with duplicate employee ids as explained below

EMP table:

EmpId Fname Lname Mname Jobcode Status exp_date

1      Mike Jordan A    IT      W      12/2014
1      Mike Jordan A    IT      A      12/2014
2      Angela ruth C    sales   P      12/2015
2      Angela ruth C    IT      W      12/2015
3      Kelly  Mike B    sales   W      12/2015

From the above table i want to select all rows which duplicate empids such as below

EmpId Fname Lname Mname Jobcode Status exp_date

1      Mike Jordan A    IT      W      12/2014
1      Mike Jordan A    IT      A      12/2014
2      Angela ruth C    sales   P      12/2015
2      Angela ruth C    IT      W      12/2015

How can I do this? thank you!

Upvotes: 4

Views: 15410

Answers (2)

sgeddes
sgeddes

Reputation: 62841

Here's another option using a subquery and COUNT OVER PARTITION BY since you're using Oracle 11:

SELECT * 
FROM (
  SELECT EmpId, Fname, Lname, Mname, Jobcode, Status, exp_date,
    COUNT(EmpId) OVER (PARTITION BY EmpId) EmpCount
  FROM    TableName 
  ) T
WHERE EmpCount > 1

SQL Fiddle Demo (Borrowed from JW)

Upvotes: 2

John Woo
John Woo

Reputation: 263723

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  EmpID
            FROM    TableName
            GROUP   BY EmpID
            HAVING  COUNT(*) > 1
        ) b ON a.EmpID = b.EmpID

Another way, although I prefer above, is to use IN

SELECT  a.*
FROM    TableName a
WHERE   EmpId IN
        (
            SELECT  EmpId
            FROM    TableName
            GROUP   BY EmpId
            HAVING  COUNT(*) > 1
        )

Upvotes: 9

Related Questions