Removing Combinations of Tuples

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Removing Combinations of Tuples

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-09 : 08:33:17

Phil writes "Hello,

I have a table with two columns: (A,B)

Here's a sample table: (1,2) (1,3) (2,1) (3,1)

I don't care the order of A and B.

In my query, How would I get it to return just (1,2) (1,3)?

Thanks,
Phil"

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-09 : 08:41:44

select a, b from table where a < b

?

-------
Moo.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-09 : 09:02:20

Generating a given rowset is one thing. Generating a given rowset based on some rules requires that the rules be communicated ...

select a,b from table where a=1 and b in (2,3)
select 1,2 union select 1,3

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-09 : 09:09:57

Or:

SELECT DISTINCT
CASE WHEN A<B THEN A ELSE B END,
CASE WHEN A>B THEN A ELSE B END
FROM myTable

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-09 : 09:57:58

or
select A,B
from mytbl t1
where not exists (select * from mytbl t2 where t2.A = t1.B and t2.B = t1.A)
or A = B

or

select A,B from mytbl where A<=B
union
select B,A from mytbl where B<A

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/09/2003 10:05:11

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-09 : 10:00:49

CREATE TABLE #TUPLES (a int, b int)

INSERT INTO #TUPLES (a,b) VALUES (1,2)
INSERT INTO #TUPLES (a,b) VALUES (2,1)
INSERT INTO #TUPLES (a,b) VALUES (3,2)
INSERT INTO #TUPLES (a,b) VALUES (2,3)
INSERT INTO #TUPLES (a,b) VALUES (4,3)

SELECT A.a, A.b
FROM #TUPLES A, #TUPLES B
WHERE A.a = B.b AND A.b = B.a AND A.a < B.a
UNION
SELECT C.a, C.b
FROM #TUPLES C
LEFT JOIN #TUPLES D ON C.a = D.b AND C.b = D.a
WHERE D.a IS NULL

DROP TABLE #TUPLES

Edited by - ValterBorges on 01/09/2003 10:05:52

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-09 : 10:34:48

quote:

Or:

SELECT DISTINCT
CASE WHEN A<B THEN A ELSE B END,
CASE WHEN A>B THEN A ELSE B END
FROM myTable

I think rob's got the answer on this one. I think he is saying it doesn't matter which value is in A, which is in B, it is just a set of two numbers and he only wants distinct sets.

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-09 : 13:52:15

The only difference would be if you want non-matched tuples ie:
(4,3) to be returned as (4,3) it gets returned as (3,4). But phil said he doesn't care about order so that should be fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources