Clash Royale CLAN TAG#URR8PPP

Return all column names for a duplicate value in a table

I would like to know is it possible to return all value for duplicate column with same ID value for oracle sql

My table design would below

Table A

Name ID Order Year
------ ------ ------- ------
JOHN 1 ORD123 2017
JAKE 2 ORD122 2018
JES 2 ORD111 2017
JOHN 3 ORD323 2012
NICK 4 ORD133 2011
AMY 4 ORD222 2010
MUS 4 ORD132 2010


I want the result of the query to be as below

Name ID Order Year
------ ------ ------- ------
JAKE 2 ORD122 2018
JES 2 ORD111 2017
NICK 4 ORD133 2011
AMY 4 ORD222 2010
MUS 4 ORD132 2010


group by having count('X') > 1

3 Answers
3

You can use an analytic function to do it in a single table scan:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TableA ( Name, ID, "Order", Year ) AS
SELECT 'JOHN', 1, 'ORD123', 2017 FROM DUAL UNION ALL
SELECT 'JAKE', 2, 'ORD122', 2018 FROM DUAL UNION ALL
SELECT 'JES', 2, 'ORD111', 2017 FROM DUAL UNION ALL
SELECT 'JOHN', 3, 'ORD323', 2012 FROM DUAL UNION ALL
SELECT 'NICK', 4, 'ORD133', 2011 FROM DUAL UNION ALL
SELECT 'AMY', 4, 'ORD222', 2010 FROM DUAL UNION ALL
SELECT 'MUS', 4, 'ORD132', 2010 FROM DUAL;


Query 1:

SELECT Name, ID, "Order", Year
FROM (
SELECT t.*,
COUNT(*) OVER ( PARTITION BY id ) AS num_duplicates
FROM tableA t
)
WHERE num_duplicates > 1


Results:

| NAME | ID | Order | YEAR |
|------|----|--------|------|
| JAKE | 2 | ORD122 | 2018 |
| JES | 2 | ORD111 | 2017 |
| NICK | 4 | ORD133 | 2011 |
| AMY | 4 | ORD222 | 2010 |
| MUS | 4 | ORD132 | 2010 |


Using IN ( SELECT ... GROUP BY id HAVING COUNT(*) > 1 ) or an aggregation in a self-join will require two table/index scans.

IN ( SELECT ... GROUP BY id HAVING COUNT(*) > 1 )

Update

if i have two condition, is it posisble also, maybe id and year?

Query 2: Just add the additional columns to the PARTITION BY clause:

PARTITION BY

SELECT Name, ID, "Order", Year
FROM (
SELECT t.*,
COUNT(*) OVER ( PARTITION BY id, year ) AS num_duplicates
FROM tableA t
)
WHERE num_duplicates > 1


Results:

| NAME | ID | Order | YEAR |
|------|----|--------|------|
| AMY | 4 | ORD222 | 2010 |
| MUS | 4 | ORD132 | 2010 |


COUNT(*) OVER ( PARTITION BY id, YEAR) AS num_duplicates

Have a sub-query to return id's that exists more than once.

select *
from tableA
where id in (select id from tableA
group by id
having count(*) > 1)


count(*)

count(id)

You can do it with either a subquery in the where clause or with a join between the duplicated ID's and the main table. For the subquery approach check jarlh's answer.

I think that for large tables you shuld make sure the table is correctly indexed.

select
a.*
from
(
select id from tableA group by id having count(*) > 1
) dupes
inner join TableA a dupes on a.id = dupes.id


count('X')

count('X')

count(*)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.