Can we use the join in the same table using group by function?

advertisements

For instance, I have a table with columns below:

pk_id,address,first_name,last_name

and I have a query like this to display the first name ans last name that are repetitive(duplicates)

select first_name,last_name
from table
group by first_name,last_name
having count(*)>1;

but the above query just returns first and last names but I want to display pk_id and address too that are tied to these duplicate first and last names Can we use joins to do this on the same table.Please help!!


A simple way of doing is to build a view with the pk_id and the count of duplicates. Once you have it, it is only a matter of using a JOIN on the base table, and a filter to only keep rows having a duplicate:

SELECT T.*
FROM T
JOIN (SELECT "pk_id",
       COUNT(*) OVER(PARTITION BY "first_name", "last_name") cnt
       FROM T) V
ON T."pk_id" = V."pk_id"
WHERE cnt > 1

See http://sqlfiddle.com/#!4/3ecd0/9