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.
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.
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;
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;