How to list rows with duplicate columns-CodePudding

I have a table with the fields id, name, birthday, clinic:

id | name | birthday   | clinic
 1 | mary | 2020-01-01 | clin 1
 2 | mary | 2020-01-01 | clin 1
 3 | mary | 2020-01-01 | clin 2
 4 | john | 2021-01-01 | clin 1
 5 | pete | 2020-01-05 | clin 1
 6 | pete | 2020-01-05 | clin 2
 7 | pete | 2020-01-05 | clin 3

I want to get all records with name, birthday duplicate like:

id | name | birthday   | clinic
 1 | mary | 2020-01-01 | clin 1
 2 | mary | 2020-01-01 | clin 1
 3 | mary | 2020-01-01 | clin 2
 5 | pete | 2020-01-05 | clin 1
 6 | pete | 2020-01-05 | clin 2
 7 | pete | 2020-01-05 | clin 3

Mary and Pete have more than one record with same name and birthday

CodePudding user response：

Using COUNT() as an analytical function, we can try:

WITH cte AS (
    SELECT *, COUNT(*) OVER (PARTITION BY name, birthday) cnt
    FROM yourTable
)

SELECT id, name, birthday, clinic
FROM cte
WHERE cnt > 1;

CodePudding user response：

You can use an EXISTS condition:

select t1.*
from the_table t1
where exists (select *
              from the_table t2
              where t1.id <> t2.id
                and (t1.name, t1.birthday) = (t2.name, t2.birthday));

CodePudding user response：

try

select * from <table> where (name , birthday)  in (
    select name , birthday from <table> group by name, birthday having count(*)>1)