r4phG r4phG - 6 months ago 14
SQL Question

SQL select duplicate rows based on multiple columns

I have created a request that returns me IDs of rows having the same column value. For example :

id | Value
_______
1 | label1
2 | label1
3 | label1
4 | label2
5 | label2


I'd like to get this kind of result :

id | AlternateID | Value
______________________________
1 | 2 | label1
1 | 3 | label1
4 | 5 | label2


The best result I got so far is that :

id | AlternateID | Value
______________________________
1 | 2 | label1
2 | 1 | label1
1 | 3 | label1
3 | 1 | label1
4 | 5 | label2
5 | 4 | label2


But as you can see, I've duplicates value across the first two columns

...Right now, without using cursors, I'm stuck.

I am on SQL Server 2008.

Thanks for your help

Answer

Use a derived table to get your base values and join it back to the original table.

SELECT 
  a.id,
  b.id as AlternateID,
  a.value
FROM 
  (SELECT MIN(id) as id , value FROM YourTable GROUP BY value) a
JOIN YourTable b on a.value = b.value and a.id <> b.id
Comments