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 |
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;
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>
SELECT
Id, first_name, last_name, age,
COUNT(*)
FROM
EMP
GROUP BY
ID,
first_name,
last_name,
age;
thank you!
1.