oracle duplicate rows based on a single column

Question

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!

John Woo · Accepted Answer · 2013-04-30 02:04:59Z

9

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

SQLFiddle Demo

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
        )

SQLFiddle Demo

answered Apr 30, 2013 at 2:04

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1751356 Over a year ago

Thanks a lot. Now could you let me know how did u improve you sql skills. I want to be like you.

sgeddes · Accepted Answer · 2013-04-30 02:14:20Z

2

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)

answered Apr 30, 2013 at 2:14

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Collectives™ on Stack Overflow

oracle duplicate rows based on a single column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related