Mohamed Sakher Sawan Mohamed Sakher Sawan - 3 months ago 8
MySQL Question

Select duplicate values in one column in a table

I have this table:

id int, name nvarchar(max), ..............


Example:

------------------
| id | name |
------------------
| 1 | Mohammed |
| 2 | Mohammed |
| 3 | Sakher |
| 4 | Sakher |
| 5 | Ahmad |
| 6 | Ahmad |
| 11 | Hasan |
| 50 | Hasan |
| 17 | Sameer |
| 19 | Soso |
| 110 | Omar |
| 113 | Omar |
| 220 | Omar |
------------------


I am trying to write a query to result this:

id1 int , id2 int , name nvarchar(max)


Example:

------------------------
| id1 | id2 | name |
------------------------
| 1 | 2 | Mohammed |
| 3 | 4 | Sakher |
| 5 | 6 | Ahmad |
| 11 | 50 | Hasan |
| 110 | 113 | Omar |
| 110 | 220 | Omar |
| 113 | 220 | Omar |
------------------------


Return the duplicates in one of the columns.
I prefer SQL Server query or standard ANSI SQL one.

Answer

This query returns what you ask for. Comparing n1.id > n2.id is better than doing it like n1.id != n2.id because this way you would get every pair twice (the second time, reversed):

SELECT 
    n1.id as Col1, n2.id as Col2, n1.name
FROM 
    Names n1, Names n2
WHERE 
    n1.name = n2.name 
    AND n1.id > n2.id