Remove duplicates - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Remove duplicates

Author

Topic

Carat
Yak Posting Veteran

92 Posts

Posted - 2009-02-06 : 05:51:47

I have a query that has the following resultset:
10254185 73175303 73233306
10254185 73233306 73175303

10285924 73035813 73283207
10285924 73283207 73035813

10293526 73037724 73105103
10293526 73105103 73037724

10301708 73313907 73341806
10301708 73341806 73313907
10301708 73341806 73313900

10316355 73175303 73202803

I only want the rows where the 2nd and 3rd column are different.
As you can see in the first 8 rows, the 2nd and 3rd column are switched. I don't want these records in my result. Only the last 2 records are valid.

How can I do this?

Thanks.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 05:56:39

can u post some data & ur query then it will better to understand

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 05:57:12

SELECT Col1, Col2, Col3
FROM (
SELECT Col1, CASE WHEN Col2 < Col3 THEN Col2 ELSE Col3 END AS Col2, CASE WHEN Col2 < Col3 THEN Col3 ELSE Col2 END AS Col3
FROM Table1
) AS f
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) = 1

E 12°55'05.63"
N 56°04'39.26"

Carat
Yak Posting Veteran

92 Posts

Posted - 2009-02-06 : 07:51:26

It works great!
Thanks Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources