Return results and filter to only combinations (?)

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)

Return results and filter to only combinations (?)

Author

Topic

ryanmcdonnell
Starting Member

5 Posts

Posted - 2002-01-08 : 18:06:26

Is it possible to return a result set that filters out similar combinations? I'm fairly novice with SQL and I haven't figured out if or how this is possible. Here are the basics:

Field1 Field2
------ ------
1 3
2 4
3 5
4 6
5 3
6 1

Where field1 = 3, field2=5 and where field1=5, field2=3. I would like to have the result set see those has similar and return only one of those rows. Possible?

I've been trying to figure out how to do this in an SP but to no avail.

Ryan McDonnell
ryan@ryanmcdonnell.com

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-08 : 18:49:58

Hi

OK here is one way. I used a case statement to swap them around so the greater of the two numbers comes out first. Then you can use distinct.



Select DISTINCT
Case
	WHEN C1.Field1 > C1.Field2 THEN C1.Field1
	ELSE C1.Field2 
END as Field1, 
Case
	WHEN C1.Field1 < C1.Field2 THEN C1.Field1
	ELSE C1.Field2
END as Field2

from Combo C1

Does that work for you ?

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-08 : 19:21:45

Ryan,

Using Damian's (Merkin) naming....


SELECT MAX(Field1) AS Field1, MAX(Field2) AS Field2 FROM Combo
GROUP BY  Field1*Field2, Field1+Field2

DavidM

Tomorrow is the same day as today was the day before.

Edited by - byrmol on 01/08/2002 19:25:13

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-08 : 19:28:58

David

Don't you mean



SELECT MAX(Field1) AS Field1, Min(Field2) AS Field2 FROM Combo
GROUP BY  Field1*Field2, Field1+Field2

Other than that....that solution rocks!

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-08 : 19:35:13

Yes I do!!!

As I was destroying brain cells as fast as possible on New Years Eve, I said to myself.. "Self don't post incorrect answers"

Oh well, another resolution butchered ..

Thanks for that Damian..

DavidM

Tomorrow is the same day as Today was the day before.

ryanmcdonnell
Starting Member

5 Posts

Posted - 2002-01-09 : 00:36:51

Thanks a bunch for the input. That solution works perfectly for me. Thanks again!

Ryan McDonnell
ryan@ryanmcdonnell.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources