Not Sure If this is a grouping question 1

[yorge]

Hi Guys,

Given the sample table below:

ID X Y
-----------------------
1 25 24
2 24 25
3 75 1
4 9 10
5 10 9
-----------------------

I wanted a result like this:

ID X Y
-----------------------
2 24 25
3 75 1
5 10 9
-----------------------

When values for X and Y exists, it will always show the higher ID value.

Thanks in advance,
Yorge

 

[gmmastros]

If I understand your question correctly, then I would do it this way:

Select  Distinct 
        Case When B.ID > A.ID Then B.Id Else A.ID End As Id,
        Case When B.ID > A.ID Then B.X Else A.X End As x,
        Case When B.Id > A.ID Then B.Y Else A.Y End as y
From    YourTableNameHere As A
        Left Join YourTableNameHere As B
          On A.X = B.Y
          And A.Y = B.X

This returns the correct data based on your sample data and your expected results.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom