i got a table users (id,first_name,last_name,...) i want to find duplicate users in that table (users which has the same first_name AND the same last_name).
let's say my data is :
1;bill;campton 2;sarah;connor 3;bill;campton i need to get
1;bill;campton;3;bill;campton i dont want to get
1;bill;campton;3;bill;campton 3;bill;campton;1;bill;campton How could i do that? I use SQL Server 2005
thank you
One way
select first_name, last_name from table group by first_name, last_name having count(*) > 1 If you want also the IDs then you can do this
SELECT t1.* FROM table t1 join (select first_name, last_name from table group by first_name, last_name having count(*) > 1) x ON t1.last_name = x.last_name AND t1.first_name = x.first_name You could use:
select u1.id, u2.id, u1.first_name, u1.last_name from users u1 inner join users u2 on u1.first_name = u2.first_name and u1.last_name = u2.last_name where u2.id > u1.id Or, to get your 6 rows, use
select u1.id, u1.first_name, u1.last_name, u2.id, u2.first_name, u2.last_name etc.
I Just figure this out. It's very simple. You can use a Common Table Expression and Window partition.
This example finds all students with the same name and DOB. The fields you want to check for duplication go in the partition. You could include what ever other fields you want in the projection.
with cte (StudentId, Fname, LName, DOB, RowCnt) as ( SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName, LastName, DateOfBirth) as RowCnt FROM tblStudent ) SELECT * from CTE where RowCnt > 1 Order By DOB 
Given the output format you said you wanted, this works:
select o.id, o.firstname, o.lastname, d.id, d.firstname, d.lastname from users o join users d on d.firstname = o.firstname and d.lastname = o.lastname and o.id < d.id Note that if you have more than one duplicate that you will get results that you probably don't want, and so SQLMenace's solution is probably much better overall.
