Removing Duplicate Pairs for Same Column

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

General SQL Server Forums

New to SQL Server Programming

Removing Duplicate Pairs for Same Column

Author

Topic

Raginrish
Starting Member

1 Post

Posted - 2009-10-11 : 03:48:13

I am stuck on a problem where I get pairs with opposing results. for example, the results i get when i do something like:
select A.somevar, B.somevar
From Relation A, Relation B
Where...

I get:
somevar somevar
test | new
new | test
blue | red
red | blue

But I Want:

somevar somevar
test | new
blue | red

If the column names are the same ("somevar" here), how do i remove these duplicate pairs? Thanks!

T

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-12 : 08:33:03

No idea what you are talking about. Please clarify.

[/fail at query]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-12 : 08:40:00

Could you post some sample date from each table, as well as the full query -- we need to know how you're joining these tables together and what's in them.

Jim

Everyday I learn something that somebody else already knew

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-10-12 : 09:03:52

If the two columns never contain the same value, then maybe:


-- *** Test Data ***
CREATE TABLE YourTable
(
	Col1 varchar(20)
	,Col2 varchar(20)
)
INSERT INTO YourTable
SELECT 'test', 'new'
UNION ALL SELECT 'new', 'test'
UNION ALL SELECT 'blue', 'red'
UNION ALL SELECT 'red', 'blue'
-- *** Test Data ***

DELETE YourTable
WHERE Col1 > Col2
AND EXISTS
(
	SELECT *
	FROM YourTable T1
	WHERE T1.col1 = YourTable.col2
		AND T1.col2 = YourTable.col1
)

SELECT *
FROM YourTable

DROP TABLE YourTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources