CODEWITHSUNDEEP
sqlsql-server
r4phG
r4phG

Reputation: 515

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

Upvotes: 1

Views: 6194

Answers (3)

JamieD77
JamieD77

Reputation: 13949

Get the min row first then outer join or outer apply to alternate values 

SELECT  mt.id, mt2.AlternateID, mt.Value
FROM    ( SELECT *, 
                 ROW_NUMBER() OVER( PARTITION BY VALUE ORDER BY id ) Rn
            FROM myTable
        ) mt
        OUTER APPLY (SELECT id [AlternateID]   -- use CROSS APPLY to only return dupes
                     FROM   myTable mt2
                     WHERE  mt2.VALUE = mt.VALUE AND mt2.id <> mt.id) mt2
WHERE   Rn = 1

Upvotes: 0

SQLChao
SQLChao

Reputation: 7847

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You seem to want pairs of ids with the same value.

with t as (
      <your query here>
     )
select t1.id as id1, t2.id as id2, t1.value
from t t1 join
     t t2
     on t1.id < t2.id and t1.value = t2.value;

Upvotes: 1

Related Questions