Search and people who have the same name, surname and date of birth

Asked

Viewed 717 times

0

How can I perform a select on Oracle that brings all people who have the same name, surname and date of birth however, have different ID’s.

sql database oracle oracle11g

  • For more help, show us your template (table and field names) and what you’ve tried (your select for example) so that we can help you.

  • The table itself is too big, it could just be a select from a fictitious table for me to understand the idea. Ex: Table = Physical Person, Fields: ID, First Name, Surname, Data_nasc

2 answers

1

Whereas the ID is unique for each record, group by these fields using GROUP BY and Filtre 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;

  • Thank you Davy Machado!! Helped a lot.

0

Broadening the @Davy Machado solution a bit

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;

Browser other questions tagged sql database oracle oracle11g

You are not signed in. Login or sign up in order to post.