CODEWITHSUNDEEP
sqlsqlite
bliof
bliof

Reputation: 2987

sql select the distinct of 2 column pair

Let's say that I have 2 columns:

_______________
| id1  |  id2 |
|------|------|
|  1   |   2  |
|  2   |   1  |
|  3   |   4  |
|  4   |   1  |
|  4   |   3  |
|  1   |   4  |
      ...

If I have a row id1 = 1 and id2 = 2, there will be a row somewhere after it which will be id1 = 2 and id2 = 1.

How can I get the top of each (id1, id2) pair?

Update:

The result from the example should be:

row1: 1 | 2
row2: 3 | 4
row3: 4 | 1

Upvotes: 4

Views: 2901

Answers (5)

Seph
Seph

Reputation: 8703

I'm pretty sure you can just do:

SELECT `id1`, `id2`
FROM `table` 
GROUP BY `id1`, `id2`
ORDER BY `id1`, `id2`

ok, to get past the (1,2) and (2, 1) from showing up:

SELECT `id1`, `id2`
FROM `table`  a
WHERE NOT EXISTS (
    SELECT TOP 1 1 
    FROM `table` b 
    WHERE b.`id1` < b.`id2` AND b.`id1` = a.`id2` AND b.`id2` = a.`id1'
)
ORDER BY `id1`, `id2`

And if you want to actually keep the order so that the first row is shown, then you will need to use a ROWID (more info: http://www.sqlite.org/autoinc.html )

SELECT id1, id2
FROM `table`  a
WHERE NOT EXISTS (
    SELECT TOP 1 1 
    FROM `table` b 
    WHERE b.ROWID < a.ROWID AND b.id1 = a.id2 AND b.id2 = a.id1
)

Results:

row1: 1 | 2
row2: 3 | 4
row3: 4 | 1

Upvotes: 0

Hogan
Hogan

Reputation: 70538

Linking back to the original table depends a lot on what is in that table that causes the order. Lets says you have a date field or a sequence field and you want the smallest value...

In this example I assume seqField is unique by row

Then it looks like this:

--drop table #test

 SELECT 1 as seqField, 1 as id1, 2 as id2
 INTO #test
 UNION ALL
 SELECT 2,2,1
 UNION ALL
 SELECT 3,3,4
 UNION ALL
 SELECT 4,4,1
 UNION ALL
 SELECT 5,4,3
 UNION ALL
 SELECT 6,1,4 
 UNION ALL
 SELECT 7,10,20;

WITH norm AS 
( 
   SELECT CASE WHEN id1 > id2 THEN id1 ELSE id2 END as a,
          CASE WHEN id1 < id2 THEN id1 ELSE id2 END as b, * 
   FROM #test
), setList AS
(
  SELECT DISTINCT a, b, min(seqField) as s
  FROM norm
  GROUP BY a, b
)
SELECT #test.* 
FROM #test
JOIN setList ON #test.seqField = setList.s

This gives

seq id1 id2
1   1   2
3   3   4
4   4   1
7   10  20

As sqlchan points out seqField can be replaced with %%physloc%% if you don't have an existing column to use.

Upvotes: 2

sqlchan
sqlchan

Reputation: 143

In SQL Server 2008...

SELECT a.col1,a.col2
FROM answer a 
JOIN answer b ON a.col1 = b.col2 and b.col1 = a.col2
and a.%%physloc%% = (SELECT MIN(c.%%physloc%%) 
                     FROM  answer c WHERE c.col1 = a.col1 and c.col2 = a.col2)
and b.%%physloc%% = (SELECT MIN(c.%%physloc%%) 
                     FROM answer c WHERE c.col1 = b.col1 and c.col2 = b.col2)
ORDER BY CASE 
     WHEN a.col1 > a.col2 
     THEN a.col2 
     ELSE b.col2 
     END

However I wouldnt use %%physloc%% (too internalesque) - I'd introduce a rowid identity column if order is important. For Oracle you can use ROWID and dont need to worry about adding a column.

Upvotes: 0

aishwarya
aishwarya

Reputation: 1986

take distinct on id1 + "|" + id2. e.g. and this is just concept, I haven't tried it right now, SELECT DISTINCT(CONCAT(id1,'|',id2)) FROM .... Seph's recommendation on group by would make more sense though

Upvotes: 0

Andomar
Andomar

Reputation: 238296

You could use a join to find the matching pair with id1, id2 inverted:

select  t2.*
from    YourTable t1
join    YourTable t2
on      t2.id1 = t1.id2
        and t2.id2 = t1.id1

Upvotes: 0

Related Questions