CODEWITHSUNDEEP
sqlt-sql
Jaimal Chohan
Jaimal Chohan

Reputation: 8645

T-Sql Query - Get Unique Rows Across 2 Columns

I have a set of data, with columns x and y. This set contains rows where, for any 2 given values, A and B, there is a row with A and B in columns x and y respectivly and there will be a second row with B and A in columns x and y respectivly.

E.g

        **Column X**            **Column Y**
Row 1        A                       B
Row 2        B                       A

I need a T-Sql query that given a set with the rules above will return me either Row 1 or Row 2, but not both.

Either the answer is very difficult, or its so easy that I can't see the forest for the trees, either way it's driving me up the wall.

Upvotes: 5

Views: 1854

Answers (4)

ChaosPandion
ChaosPandion

Reputation: 78262

This should work.

Declare @t Table (PK Int Primary Key Identity(1, 1), A int, B int);

Insert into @t values (1, 2);
Insert into @t values (2, 1);
Insert into @t values (3, 4);
Insert into @t values (4, 3);
Insert into @t values (5, 6);
Insert into @t values (6, 5);

Declare @Table Table (ID Int Primary Key Identity(1, 1), PK Int, A Int, B Int);
Declare @Current Int;
Declare @A Int;

Insert Into @Table 
Select PK, A, B 
From @t;

Set @Current = 1;    

While (@Current <= (Select Max(ID) From @Table) Begin    

    Select @A = A 
    From @Table 
    Where ID = @Current;        

    If (@A Is Not Null) Begin

        Delete From @Table Where B = @A;            
        If ((Select COUNT(*) From @Table Where A = @A) > 1) Begin
            Delete From @Table Where ID = @Current;
        End

    End

    Set @A = Null;  
    Set @Current = @Current + 1;

End

Select a.*
From @tAs a
    Inner Join @Table As b On a.PK = b.PK

Upvotes: 1

tpdi
tpdi

Reputation: 35141

Add to your query the predicate,

where X < Y

and you can never get row two, but will always get row one.

(This assumes that when you wrote "two given values" you meant two distinct given values; if the two values can be the same, add the predicate where X <= Y (to get rid of all "reversed" rows where X > Y) and then add a distinct to your select list (to collapse any two rows where X == Y into one row).)

In reply to comments:

That is, if currently your query is select foo, x, y from sometable where foo < 3; change it to select foo, x, y from sometable where foo < 3 and x < y;, or for the the second case (where X and Y are not distinct values) select distinct foo, x, y from sometable where foo < 3 and x <= y;.

Upvotes: 9

Rob Farley
Rob Farley

Reputation: 15849

To get the highest and lowest of each pair, you could use:

(X+Y+ABS(X-Y)) / 2 as High, (X+Y-ABS(X-Y)) / 2 as Low

So now use DISTINCT to get the pairs of them.

SELECT DISTINCT 
  (X+Y+ABS(X-Y)) / 2 as High, (X+Y-ABS(X-Y)) / 2 as Low 
FROM YourTable

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

SELECT O.X, O.Y
FROM myTable O
WHERE EXISTS (SELECT X, Y FROM myTable I WHERE I.X = O.Y AND I.Y = O.X)

I have not tried this. But, this should work.

Upvotes: 0

Related Questions