CODEWITHSUNDEEP
sqloracle-database
Bob Bobson
Bob Bobson

Reputation: 11

SQL: Finding values in sets, that only appear once

Using Oracle SQL, I need to find the IDs (ICFPROKEYI) that occur more then once, but have a certain field (ICFFLDC) only once:

ICFPROKEYI|ICFKAVKEYI|ICFNUMS|ICFFLDC                      
----------|----------|-------|-----------------------------

   2234884|      5887|      0|Farbe.14870                  
   2234884|      5887|      1|Ueberschrift_i_24291101.18563
   2234884|      5888|      0|Farbe.14870                  
   2234884|      5889|      0|Farbe.14870                  
   2234884|      5890|      0|Farbe.14870                  
   2234884|      5896|      0|Farbe.14870

In this case, 2234884, because it appears 6 times but has a value (Ueberschrift_i_24291101.18563) appear only once

Upvotes: 1

Views: 93

Answers (6)

Pawan Prasad
Pawan Prasad

Reputation: 70

I have replicated this on my local, please find the below SQL block and try it yourself



drop table test;

CREATE TABLE test(
   ICFPROKEYI INTEGER  NOT NULL 
  ,ICFKAVKEYI INTEGER  NOT NULL
  ,ICFNUMS    number(1,0)  NOT NULL
  ,ICFFLDC    VARCHAR(30) NOT NULL
);
INSERT INTO test(ICFPROKEYI,ICFKAVKEYI,ICFNUMS,ICFFLDC) VALUES (2234884,5887,0,'Farbe.14870');
INSERT INTO test(ICFPROKEYI,ICFKAVKEYI,ICFNUMS,ICFFLDC) VALUES (2234884,5887,1,'Ueberschrift_i_24291101.18563');
INSERT INTO test(ICFPROKEYI,ICFKAVKEYI,ICFNUMS,ICFFLDC) VALUES (2234884,5888,0,'Farbe.14870');
INSERT INTO test(ICFPROKEYI,ICFKAVKEYI,ICFNUMS,ICFFLDC) VALUES (2234884,5889,0,'Farbe.14870');
INSERT INTO test(ICFPROKEYI,ICFKAVKEYI,ICFNUMS,ICFFLDC) VALUES (2234884,5888,0,'Farbe.14870');
INSERT INTO test(ICFPROKEYI,ICFKAVKEYI,ICFNUMS,ICFFLDC) VALUES (2234884,5889,0,'Farbe.14870');
INSERT INTO test(ICFPROKEYI,ICFKAVKEYI,ICFNUMS,ICFFLDC) VALUES (2234885,5890,0,'Farbe.14870');
INSERT INTO test(ICFPROKEYI,ICFKAVKEYI,ICFNUMS,ICFFLDC) VALUES (2234885,5896,0,'Farbe.14870');

Query 

SELECT A.ICFPROKEYI FROM (select ICFPROKEYI,ICFFLDC, count(*) AS ICFFLDC_CNT from test 
group by ICFPROKEYI,ICFFLDC)A LEFT OUTER JOIN 
(select ICFPROKEYI,count(*) as ICFPROKEYI_CNT from test group by ICFPROKEYI)B 
ON A.ICFPROKEYI = B.ICFPROKEYI WHERE A.ICFFLDC_CNT = 1AND B.ICFPROKEYI_CNT > 1 ;

Upvotes: 0

Popeye
Popeye

Reputation: 35910

You can simply use GROUP BY with HAVING (two conditions) as following:

SELECT
    ICFPROKEYI
FROM <yourtable>
GROUP BY ICFPROKEYI
HAVING COUNT(1) > 1
       AND SUM(CASE WHEN ICFFLDC = 'Ueberschrift_i_24291101.18563' 
                    THEN 1 END) = 1

-- Update

After @ankit specified that it can be any value(not fixed value - Ueberschrift_i_24291101.18563), OP can achieve the desired result using following query:

SELECT ICFPROKEYI
FROM
    ( SELECT T.ICFPROKEYI,
            COUNT(1) OVER(
                PARTITION BY T.ICFPROKEYI, ICFFLDC
            ) AS CNT
        FROM YOURTABLE T
    )
WHERE CNT = 1

Cheers!!

Upvotes: 0

Grace Brown
Grace Brown

Reputation: 81

I've tried this with MySQL and might be applicable in oracle as well.

SELECT ICFPROKEYI
FROM <yourtable> WHERE ICFPROKEYI IN
(
    SELECT ICFPROKEYI
    FROM <yourtable>
    GROUP BY ICFPROKEYI
    HAVING COUNT(ICFPROKEYI) > 1
)
GROUP BY ICFPROKEYI, ICFFLDC
HAVING cnt(ICFFLDC) = 1

Upvotes: 0

RKT
RKT

Reputation: 314

Here is the postgres query:

select ICFPROKEYI,ICFFLDC from table group by ICFPROKEYI,ICFFLDC having count(*)=1;

Help yourself to write the oracle equivalent for it.

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

GROUP BY icfprokeyi and use HAVING count(*) > 1 to get the icfprokeyi that appear more than once and GROUP BY icfprokeyi, icffldc and use HAVING count(*) = 1 to get the icfprokeyi where the icffldc doesn't exist in another row with the same icfprokeyi. Then join both aggregations.

SELECT x1.icfprokeyi
       FROM (SELECT t1.icfprokeyi
                    FROM elbat t1
                    GROUP BY t1.icfprokeyi
                    HAVING count(*) > 1) x1
            INNER JOIN (SELECT t2.icfprokeyi
                               FROM elbat t2
                               GROUP BY t2.icfprokeyi,
                                        t2.icffldc
                               HAVING count(*) = 1) x2
                       ON x2.icfprokeyi = x1.icfprokeyi;

Upvotes: 1

Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

You can try this:

select a.ICFPROKEYI from table a join table b
on a.ICFPROKEYI = b.ICFPROKEYI and a.ICFFLDC <> b.ICFFLDC

Upvotes: 1

Related Questions