CODEWITHSUNDEEP
sqloracle-databasegroup-byaggregate-functionshaving
debegris
debegris

Reputation: 115

How can I select unique and duplicated rows exlude with different values

How can I fetch this table as expected in Oracle. I'm try to do this like below select but its not give me the right result. What I expect is fetch only the unique ones and exclude if these is a record like different values. Sorry for if asked before but I couldn't find it.

SELECT * 
FROM ...
WHERE number IN ( SELECT name
                  FROM (SELECT *
                        FROM table
                        WHERE number IN ('Mel','Jose','Kim')
                  ) ds
                  GROUP BY number
                  HAVING COUNT (*) = 1)

Current result: 

 number name
   aaa   Mel
   asd   Jose
   fsa   Jose
   xdf   Jose
   zzz   Kim
   zzz   Kim

Expected result:

aaa Mel
zzz Kim

Upvotes: 0

Views: 47

Answers (2)

Boneist
Boneist

Reputation: 23578

You're close - I think you were just missing the distinct in the count in your having clause.

E.g.:

WITH your_table AS (SELECT 100 nmbr, 'Mel' NAME FROM dual UNION ALL
                    SELECT 112 nmbr, 'Jose' NAME FROM dual UNION ALL
                    SELECT 212 nmbr, 'Jose' NAME FROM dual UNION ALL
                    SELECT 313 nmbr, 'Jose' NAME FROM dual UNION ALL
                    SELECT 101 nmbr, 'Kim' NAME FROM dual UNION ALL
                    SELECT 101 nmbr, 'Kim' NAME FROM dual)
-- end of mimicking data in your table
-- you already have this table, so you would just need the below sql:
SELECT min(nmbr) nmbr,
       NAME
FROM   your_table
GROUP BY NAME
HAVING COUNT(DISTINCT nmbr) = 1;

      NMBR NAME
---------- ----
       101 Kim
       100 Mel

Just to prove that it doesn't matter whether the nmbr column is of NUMBER or VARCHAR2 datatype:

WITH your_table AS (SELECT 'aaa' nmbr, 'Mel' NAME FROM dual UNION ALL
                    SELECT 'asd' nmbr, 'Jose' NAME FROM dual UNION ALL
                    SELECT 'fsa' nmbr, 'Jose' NAME FROM dual UNION ALL
                    SELECT 'xfd' nmbr, 'Jose' NAME FROM dual UNION ALL
                    SELECT 'zzz' nmbr, 'Kim' NAME FROM dual UNION ALL
                    SELECT 'zzz' nmbr, 'Kim' NAME FROM dual)
-- end of mimicking data in your table
-- you already have this table, so you would just need the below sql:
SELECT min(nmbr) nmbr,
       NAME
FROM   your_table
GROUP BY NAME
HAVING COUNT(DISTINCT nmbr) = 1;

NMBR NAME
---- ----
zzz  Kim
aaa  Mel

Upvotes: 4

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can do by nested sql with the inner part eliminate repeating ones with respect to id & name, and in the outer part eliminate repeating ones with only name like in the following statement :

SELECT MAX(id),name 
FROM (SELECT id,name FROM mytable GROUP BY id, name)  
GROUP BY name 
HAVING COUNT(1) = 1
ORDER BY MAX(id);

OUTPUT:

ID       NAME
-----    ------
100      Mel
101      Kim

D e m o 1

exactly the same sql works for your second case :

D e m o 2

Upvotes: 2

Related Questions