Oracle duplicate row detection

Question

What is the ideal way to display duplicate rows in Oracle? The trick here is that I am looking for all rows no just the multiples (duplicates). So for example on the below data set:

RSN fname   lname   emailaddress
1   John    Smith   [email protected]
2   John    Smith   [email protected]
3   John    Smith   [email protected]
4   Kevin   Walker  [email protected]
5   James   Kirk    [email protected]
6   James   Kirk    [email protected]
7   Kevin   James   [email protected]
8   Mike    Jones   [email protected]

I would want the following returned:

1   John    Smith   [email protected]
2   John    Smith   [email protected]
3   John    Smith   [email protected]
5   James   Kirk    [email protected]
6   James   Kirk    [email protected]

Any help?

I searched extensively, however, the results I found returned only the duplicates, that is the rows that are the second or higher instance of a given criteria. But thanks for the input. — b1shp
– b1shp, Commented Jul 24, 2013 at 19:10

ruakh · Accepted Answer · 2013-07-24 18:51:44Z

1

Here's one way:

SELECT RSN, fname, lname, emailaddress
  FROM whatever_your_table_is_named t1
 WHERE ( SELECT COUNT(1)
           FROM whatever_your_table_is_named t2
          WHERE t2.fname = t1.fname
            AND t2.lname = t1.lname
            AND t2.emailaddress = t1.emailaddress
            AND ROWNUM < 3
       ) > 1
;

Here's another:

SELECT t1.RSN, t1.fname, t1.lname, t1.emailaddress
  FROM whatever_your_table_is_named t1
  JOIN ( SELECT fname, lname, emailaddress
           FROM whatever_your_table_is_named
          GROUP
             BY fname, lname, emailaddress
         HAVING COUNT(1) > 1
       ) t2
    ON t1.fname = t2.fname
   AND t1.lname = t2.lname
   AND t1.emailaddress = t2.emailaddress
;

(Disclaimer: I haven't tested either of these.)

answered Jul 24, 2013 at 18:51

ruakh

185k29 gold badges292 silver badges324 bronze badges

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

11 Comments

b1shp Over a year ago

Thanks, I have no idea how optimal it is, since obviously my Oracle skills aren't great, but it definitely works. Thanks ruakh!

David Aldridge Over a year ago

That count(1) is ugly. It's non-standard and no better than count(*). Rumours of its superiority performance-wise are just myths.

b1shp Over a year ago

Well any improvements are very welcome. I'll be running this against over a million records.

ruakh Over a year ago

@DavidAldridge: I'm aware that there's no performance advantage of COUNT(1) over COUNT(*) -- nor disadvantage, they're simply equivalent -- but I often use COUNT to count non-null values, so I find COUNT(1) (meaning "count the records where 1 is non-null") to be more logical than COUNT(*) (meaning "OMG magical syntax that costs an extra keystroke and looks like it should have something to do with other uses of * even though it doesn't"). When you say it's "ugly" and "non-standard", what do you mean by that?

David Aldridge Over a year ago

count(*) means "count the records" according to the ANSI SQL standard (and the documentation), so by definition anything else -- count(1), count(0), count('X') -- is non-standard. As I say, there's a myth that count(1) is faster than count(*), so every time a person who believes in the myth sees it, it reinforces their false belief. When a person who has never heard of the myth sees it, it can lead to an "investigation" that converts them to a false belief. I think it should be discouraged, having as it does these disadvantages and no advantages.

|

Carl Peyton · Accepted Answer · 2013-07-24 19:37:48Z

0

this subquery will get the duplicates form the table.

    select *
    from (some table)
    where rsn IN (select rsn
                  from (some table) 
                   where fname IN 
                   (select fname
                    from (select fname, count(fname) as dup 
           from (some table)) where dup > 1)

answered Jul 24, 2013 at 19:37

Carl Peyton

421 silver badge12 bronze badges

Collectives™ on Stack Overflow

Oracle duplicate row detection

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related