Question: Find Duplicate in column b for each value in column A Oracle

Question

Find Duplicate in column b for each value in column A Oracle

Answers 2
Added at 2017-01-05 09:01
Tags
Question

I have Table like Below:

Sr_No   C_A  C_B
-------------------
1       100  A
2       100  A
3       100  B
4       101  A
5       102  A
6       102  B
7       103  A
8       103  A
9       103  B

And I want select query to get below

C_A 
----
100 
103 

I want to know how many records are in C_A column having duplicate values in C_B column.

Answers to

Find Duplicate in column b for each value in column A Oracle

nr: #1 dodano: 2017-01-05 09:01

The query below checks, for each C_A group, that the number of distinct C_B values is less than the total number of values. This condition implies that all C_B values are not unique for a given C_A group, and that there are duplicates.

SELECT C_A
FROM yourTable
GROUP BY C_A
HAVING COUNT(DISTINCT C_B) < COUNT(*)
nr: #2 dodano: 2017-01-05 09:01

If I understand well, you may need:

select distinct C_A
from 
    (select C_A, C_B, count(1) over ( partition by C_A, C_B) as cnt
     from test
    )
where cnt > 1

The nested query counts the number of duplicates for each couple of values in C_A, C_B, while the external one simply filters this result to only get couples with duplicates

Source Show
◀ Wstecz