CODEWITHSUNDEEP
sqloracle-database
sam2539431
sam2539431

Reputation: 41

Oracle sql to find single row from duplicates

Need help on what will be the sql if I want to find contacts which has only single ruleid which is 2. Hence the output should be contact id's 1 and 7. I am not looking for contact Id 5 & 6 in the output as they have more than one ruleid even though they have got ruleid 2

Contactid   ruleid
1                   2
5                   3
5                   2
6                   4
6                   2
7                   2

Can someone please help on this.

Upvotes: 3

Views: 79

Answers (4)

Oto Shavadze
Oto Shavadze

Reputation: 42763

WITH your_table_name as(
        SELECT 1 AS Contactid   , 2 AS ruleid FROM DUAL
        UNION ALL
        SELECT 5 AS Contactid   , 3 AS ruleid FROM DUAL
        UNION ALL
        SELECT 5 AS Contactid   , 2 AS ruleid FROM DUAL
        UNION ALL
        SELECT 6 AS Contactid   , 4 AS ruleid FROM DUAL
        UNION ALL
        SELECT 6 AS Contactid   , 2 AS ruleid FROM DUAL
        UNION ALL
        SELECT 7 AS Contactid   , 2 AS ruleid FROM DUAL
)

SELECT t.* FROM (
      SELECT   Contactid   FROM  your_table_name
      WHERE ruleid = 2
      GROUP BY   Contactid 
      HAVING COUNT(*) = 1
    ) t
    left JOIN (
      SELECT   Contactid   FROM  your_table_name
      WHERE ruleid <> 2
      GROUP BY   Contactid 
    ) tt
    ON
    t.Contactid = tt.Contactid
    where tt.Contactid is null

Upvotes: 0

Renato Afonso
Renato Afonso

Reputation: 654

This is not enough for you?

select contactid from table group by contactid having count(ruleid)=1

Upvotes: 0

user5683823
user5683823

Reputation:

Something like this should work and will be efficient.

select   contactid
from     table_name
group by contactid
having   min(ruleid) = 2 
     and max(ruleid) = 2

Upvotes: 0

nbouchet
nbouchet

Reputation: 211

You could try something like this:

SELECT Contactid FROM <YOURTABLE> 
WHERE ruleid = 2 
AND Contactid NOT IN 
    (SELECT Contactid FROM <YOURTABLE> 
     WHERE ruleid <> 2)

Upvotes: 2

Related Questions