0

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
gollumsoprano
  • 11
  • 2
  • Does this answer your question? [SQL multiple column ordering](https://stackoverflow.com/questions/2051162/sql-multiple-column-ordering) – David Brossard Sep 06 '22 at 23:39

2 Answers2

0

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
VLOOKUP
  • 548
  • 4
  • 12
0
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
first_name last_name
Alma Slor

Fiddle

DannySlor
  • 4,574
  • 1
  • 3
  • 11