Geek Answers Handbook

T-Sql Query - get unique rows through 2 columns

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

eg

        **Column X**            **Column Y**
Row 1        A                       B
Row 2        B                       A
  • There are several data pairs in this set that follow this rule.
  • For each row with A, B in columns X and Y there will always be a row with B, A in X and Y
  • Columns X and Y are of type int

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

Either the answer is very complex, or it is so easy that I can not see the forest for trees, as it pushes me against the wall.

+5
4

where X < Y

, .

(, " ", : , where X <= Y ( "" , X > Y), distinct ( , X == Y ).

:

, select foo, x, y from sometable where foo < 3;, select foo, x, y from sometable where foo < 3 and x < y; ( X Y ) select distinct foo, x, y from sometable where foo < 3 and x <= y;.

+9

.

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
+1

, :

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

, DISTINCT, .

SELECT DISTINCT 
  (X+Y+ABS(X-Y)) / 2 as High, (X+Y-ABS(X-Y)) / 2 as Low 
FROM YourTable
0
source 
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 that should work.

0
source

More articles:


All Articles