Page 1 of 1

Selecting distinct pairs

Posted: Wed Oct 29, 2008 6:40 pm
by legend986
I have a table like:

Code: Select all

 
Column A | Column B
1 2
1 3
1 5
2 1
5 1
 


I am trying to select just the unique pairs from the above table to give me:

Code: Select all

 
1 2
1 3
1 5
 
Can someone please help me out?

Re: Selecting distinct pairs

Posted: Wed Oct 29, 2008 6:52 pm
by VladSun
[sql]SELECT       a, b FROM     tGROUP BY     (a*b), (a+b)[/sql]

Re: Selecting distinct pairs

Posted: Wed Oct 29, 2008 7:02 pm
by legend986
Perfect! Thank you so much... Really nice logic...

Re: Selecting distinct pairs

Posted: Wed Oct 29, 2008 7:12 pm
by legend986
Just a final extension... Assuming that I have done that on two tables, I want to select the intersection rows. So something like:

Code: Select all

 
Table 1
1 2
1 5
2 3 
4 5
 

Code: Select all

 
Table 2
2 1
8 9
 
When I query for the column1=1, I want to get only those elements from Table 1 that are not in Table 2 in either format so in this case, I should get only 1,5 because either 1,5 or 5,1 do not exist in Table 2.

Re: Selecting distinct pairs

Posted: Wed Oct 29, 2008 7:18 pm
by VladSun
It's your turn to show us some code ;)

Re: Selecting distinct pairs

Posted: Wed Oct 29, 2008 7:20 pm
by legend986
Yes... :) I am trying it in parallel here... Will post it the moment I get it.... Thank You for encouraging...

Re: Selecting distinct pairs

Posted: Wed Oct 29, 2008 7:37 pm
by legend986
Ok... I think I got it... Here's my solution:

Code: Select all

 
SELECT * from table1 
LEFT JOIN 
(SELECT column1,column2 FROM table2 UNION SELECT column2,column1 from table2) as TEMP 
USING (column1,column2) 
WHERE (TEMP.column1 IS NULL) AND (table1.column1=1 OR table1.column2=1);
 
I am getting the correct result using my test tables but I wanted to confirm it from you before I use it on my original dataset. If the solution is correct, thank you for not providing the solution and making me think. If it isn't, I hope you can direct me a little more. :)

Re: Selecting distinct pairs

Posted: Wed Oct 29, 2008 7:52 pm
by VladSun
Looks good to me :)
legend986 wrote:... thank you for not providing the solution and making me think ...
You are welcome ... anytime! :)