CODEWITHSUNDEEP
sqloracle-databaseselectduplicates
Mr. Jin
Mr. Jin

Reputation: 79

I am looking find dups

I am trying to filter out the dups writing a query in Oracle TOAD SQL. Below is the data I have:

Primary          T_ID           C_ID
2039515494      276162453       20028
2176514662      276721971       5508
2176514662      276721972       5508

I am looking to have only 

 Primary          T_ID          C_ID
2176514662      276721971       5508
2176514662      276721972       5508

Upvotes: 1

Views: 58

Answers (3)

Darzen
Darzen

Reputation: 1135

This assumes that you are looking for rows with duplicate 'primary' values;

select * from t where primary in 
(select primary from t group by(primary) having count(primary) > 1);

This assumes you want to consider both primary and c_id

with t1 as (select primary, c_id from t group by(primary, c_id) having count(*) > 1) 
select t.primary, t.t_id, t.c_id from t, t1 where t.c_id = t1.c_id and t.primary = t1.primary;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use a simple exists, I think:

select t.*
from t
where exists (select 1 from t t2 where t2.primary = t.primary and t2.t_id <> t.t_id);

This assumes that t_id is unique. You might want to include c_id as well for the comparisons.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311393

Using the window (analytic) variant of count could help you:

SELECT primary, t_id, c_id
FROM   (SELECT primary, t_id, c_id, COUNT(*) OVER (PARTITION BY primary, c_id) AS cnt
        FROM   mytable) t
WHERE  cnt > 1

Upvotes: 2

Related Questions