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 :

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 have duplicate values 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

Advertisement

Answer