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:
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:
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!
