I have a table with last and first names in separate columns. I need to return all rows containing duplicate last and first names. I have this so far and it works to filter by last name only but not sure how to filter by the first name too. Would appreciate some guidance. Thanks!

with cte1 as (
  select 
    distinct lastName as last_name,
    count(lastName) over (partition by lastName) as ln_count
  from peoplelist
),

cte2 as (
  select 
    ng.*
    from
     peoplelist ng
    left join 
      cte1 on cte1.last_name = ng.LastName
    where cte1.ln_count > 1
    order by LastName desc
)
select * from cte2

CodePudding user response：

You could concat the names together and just perform a count by full name and add the full name with a group by and having > 1

SELECT 
CONCAT(FIRSTNAME, ' ', LastName) AS FULLNAME
, COUNT(CONCAT(FIRSTNAME, ' ', LastName)) AS FULLNAMECOUNT
FROM
PEOPLELIST
GROUP BY CONCAT(FIRSTNAME, ' ', LastName)
HAVING COUNT(CONCAT(FIRSTNAME, ' ', LastName)) > 1

CodePudding user response：

select first_name
      ,last_name
from   (
       select *
            ,row_number() over(partition by first_name,last_name order by first_name) as rn
       from t
       ) t
where  rn > 1

Fiddle

CodePudding user response：

select distinct(CONCAT(FirstName , ' ', LastName)) from names
where 
FirstName not in (select FirstName from names group by FirstName having count(*) = 1) 
and 
LastName not in (select LastName from names group by LastName having count(*) = 1)