CODEWITHSUNDEEP
sqlsql-serverdatabase
Programmer
Programmer

Reputation: 11

Display all duplicated values and their IDs from a table (SQL)

I'm using SQL Sever. I have a table that looks like this:

+---------+--------------+
| ID         Name        |  
+---------+--------------+
| 1       | John   
| 2       | Charles  
| 3       | Molly  
| 4       | John
| 5       | Kathy
| 6       | Mike
| 7       | Charles
| 8       | Kathy

Is there a way I can retrieve all the duplicated values only? Not just one of them, but all.

This is the desired output:

+---------+--------------+
| ID         Name        |  
+---------+--------------+
| 1       | John     
| 4       | John
| 5       | Kathy
| 8       | Kathy
| 7       | Charles
| 2       | Charles

Upvotes: 0

Views: 64

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I strongly recommend using exists:

select t.*
from t
where exists (select 1 from t t2 where t2.name = t.name and t2.id <> t.id);

With an index on (name, id), this should have the best performance.

Upvotes: 0

Павел
Павел

Reputation: 1

It works for SQL Server:

select t.ID, t.Name
from
(select ID as 'ID', 
       Name as 'Name',
       row_number() over (partition by Name order by Name asc) as 'rn'
from table ) t
where t.rn > 0

Upvotes: 0

Giorgos Oikonomou
Giorgos Oikonomou

Reputation: 11

Check if this helps. 

SELECT t0.ID,t0.Name
FROM @YourTable t0
INNER JOIN (SELECT
           Name, COUNT(*) AS CountOf
           FROM @YourTable
           GROUP BY Name
           HAVING COUNT(*)>1
           ) 
t1 ON t0.name=t1.name

Upvotes: 0

Chris Albert
Chris Albert

Reputation: 2507

This works in SQL Server.

SELECT Id, Name
FROM MyTable AS T1
WHERE
    EXISTS
    (
        SELECT Name
        FROM MyTable
        WHERE Name = T1.Name
        GROUP BY Name
        HAVING COUNT(*) > 1
    );

Upvotes: 1

Related Questions