How Do I Find Duplicates For Multiple Column Combinations in Oracle-CodePudding

Suppose I have a table emp with the following data

Id	first_name	last_name	age
1	John	Doe	20
2	Jane	Smith	90
3	John	Doe	39
4	Jane	Smith	47
5	Jane	Doe	89

I want a query that returns first_name, last_name duplicate combos i.e.

Id	first_name	last_name	Age
1	John	Doe	20
3	John	Doe	39
2	Jane	Smith	90
4	Jane	Smith	47

CodePudding user response：

It seems you simply need a COUNT() OVER() window function -

SELECT ROWNUM, first_name, last_name, age
  FROM (SELECT first_name, last_name, age, COUNT(*) OVER(PARTITION BY first_name, last_name) CNT
          FROM Your_table)
 WHERE CNT > 1;

CodePudding user response：

This is one option.

Sample data:

SQL> with emp (id, first_name, last_name, age) as
  2    (select 1, 'John', 'Doe'  , 20 from dual union all
  3     select 2, 'Jane', 'Smith', 90 from dual union all
  4     select 3, 'John', 'Doe'  , 39 from dual union all
  5     select 4, 'Jane', 'Smith', 47 from dual union all
  6     select 5, 'Jane', 'Doe'  , 89 from dual
  7    )

Query begins here:

  8  select *
  9  from emp
 10  where (first_name, last_name) in (select first_name, last_name
 11                                    from emp
 12                                    group by first_name, last_name
 13                                    having count(*) > 1
 14                                   )
 15  order by first_name, last_name;

        ID FIRS LAST_        AGE
---------- ---- ----- ----------
         4 Jane Smith         47
         2 Jane Smith         90
         3 John Doe           39
         1 John Doe           20

SQL>

CodePudding user response：

SELECT 
    Id, first_name, last_name, age,
    COUNT(*)
FROM 
    EMP
GROUP BY 
    ID,
    first_name,
    last_name, 
    age;

thank you!