SQL select duplicate rows based on multiple columns

Question

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

Can you explain the logic behind the numbers? It is not obvious to me. — Gordon Linoff
– Gordon Linoff, Commented May 31, 2016 at 19:57
We can not tell what you are really looking for here. Are you wanting to group by and get a count? — Chuck
– Chuck, Commented May 31, 2016 at 19:58
the first table numbers correspond to the id of the row. the second table is like a Map. For example, ID 1 can be used instead ID 2 and ID 3 / ID 4 can ben used instead of ID5 I guess there is something around a group by clause... but I am really stuck ... — r4phG
– r4phG, Commented May 31, 2016 at 19:58
Please update your question or add some code how you got to the first table. It's a little hard to understand what you are trying to do. — NKD
– NKD, Commented May 31, 2016 at 20:00
You may want to change the name of the column "Value" in your desired result. I think it's throwing some people (including me) off. Maybe update it to "AlternateID" or something along those lines? Might help reduce some confusion. — user4650451
– user4650451, Commented May 31, 2016 at 20:03

SQLChao · Accepted Answer · 2016-05-31 20:26:59Z

2

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

answered May 31, 2016 at 20:26

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

r4phG Over a year ago

This was the solution ! Thank you very much !

Gordon Linoff · Accepted Answer · 2016-05-31 19:59:22Z

1

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;

answered May 31, 2016 at 19:59

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

3 Comments

r4phG Over a year ago

The issue of the solution is that I get this 1 | 2 | label1 // 2 | 1 | label1 .... I got also this result with a join :/

Gordon Linoff Over a year ago

@r4phG . . . Not at all. The t1.id < t2.id prevents two such rows from being generated.

r4phG Over a year ago

You are right, I've used t1.id <> t2.id which caused the issue. Sorry and thank you

JamieD77 · Accepted Answer · 2016-05-31 20:32:19Z

0

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

edited May 31, 2016 at 20:32

answered May 31, 2016 at 20:22

JamieD77

14k1 gold badge19 silver badges27 bronze badges

4 Comments

r4phG Over a year ago

Sorry, maybe this could work, but I couldn't get this query working :/

JamieD77 Over a year ago

did you try using your actual table name?

r4phG Over a year ago

^^. Of course ! I have a problem around the OVER( PARTITION ...) Rn

JamieD77 Over a year ago

lol you might be on older version of sql server or the compatibility mode is still set to 2005.. sqlfiddle.com/#!3/2e400d/1

Collectives™ on Stack Overflow

SQL select duplicate rows based on multiple columns

3 Answers 3

1 Comment

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related