CODEWITHSUNDEEP
sql
user3599884
user3599884

Reputation: 11

Show duplicate rows

I got a list with names and IP addresses like this:

ID | Name     | Address
1  | Peter    | 111.222.333
2  | John     | 999.888.333
3  | Hans     | 111.222.333
4  | Kimberly | 555.555.1111

I'm trying to find a way to display the duplicate accounts like this with SQL:

Name  | Second account (found by IP)
Peter | Hans

Please help,

Thank you.

Upvotes: 1

Views: 77

Answers (2)

dnoeth
dnoeth

Reputation: 60513

If you got more than 2 duplicates 

1  | Peter    | 111.222.333
2  | John     | 111.222.333
3  | Hans     | 111.222.333

fancyPants's query will return all possible combinations like

Name  | Second account (found by IP)
Peter | Hans
Peter | John
John  | Hans

This will return a list of duplicates, not pairs:

select address, name
from tab
where address in
  ( select address
    from tab
    group by address
    having count(*) > 1
  )

Depending on your needs this might be ok, too:

select min(name), max(name), count(*) as number_of_duplicates
from tab
group by address
having count(*) > 1

Upvotes: 1

fancyPants
fancyPants

Reputation: 51948

You just need to join the table to itself.

select
t1.name, t2.name as second_account
from
your_table t1
inner join your_table t2 on t1.address = t2.address and t1.name < t2.name

Upvotes: 1

Related Questions