Search and persons having the same name, surname and date of birth [closed]

0

How can I make a select in Oracle that brings all people who have the same name, surname and date of birth but have different ID's.

    
asked by anonymous 31.03.2017 / 15:58

2 answers

1

Since the ID is unique for each record, group those fields using GROUP BY and filter the records with more than one occurrence using HAVING.

SELECT NOME, SOBRENOME, DATA_NASCIMENTO FROM PESSOAS 
GROUP BY NOME, SOBRENOME, DATA_NASCIMENTO HAVING COUNT(*) > 1;

If you want to view the records as a whole, one option is:

SELECT 
  A.* 
FROM 
  PESSOAS A JOIN PESSOAS B ON A.NOME = B.NOME AND A.SOBRENOME = B.SOBRENOME 
    AND A.DATA_NASCIMENTO = B.DATA_NASCIMENTO AND A.ID <> B.ID;
    
31.03.2017 / 16:17
0

Extending the @Davy Machado solution a little

SELECT *
FROM PESSOAS 
WHERE (NOME, SOBRENOME, DATA_NASCIMENTO) IN (SELECT NOME, SOBRENOME, DATA_NASCIMENTO 
                                             FROM PESSOAS 
                                             GROUP BY NOME, SOBRENOME, DATA_NASCIMENTO 
                                             HAVING COUNT(DISTINCT ID) > 1)
ORDER BY NOME, SOBRENOME, DATA_NASCIMENTO , ID;
    
31.03.2017 / 19:40