CODEWITHSUNDEEP
sqlsql-server
storm_buster
storm_buster

Reputation: 7568

sql : how to find duplicates rows?

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

Upvotes: 0

Views: 13398

Answers (4)

Darrel Lee
Darrel Lee

Reputation: 2460

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

Upvotes: 1

Donnie
Donnie

Reputation: 46903

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.

Upvotes: 0

Spiny Norman
Spiny Norman

Reputation: 8327

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.

Upvotes: 6

SQLMenace
SQLMenace

Reputation: 134923

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

Upvotes: 12

Related Questions