Eliminating Unwanted Duplicates from Resultset

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)

Eliminating Unwanted Duplicates from Resultset

Author

Topic

EssKay
Starting Member

1 Post

Posted - 2010-04-28 : 05:42:16

Hello Experts,

I have a resultset with a format as below:



Row     Number_1    Number_2

1000	 123	       789
1001	 789	       123
1002	 234	       678
1003	 234	       567
1004	 678	       234
1005	 567	       234

Now what I need to do is to select only the unique combinations of columns "Number_1" and "Number_2". This means that when I select the "Row" 1000 above, I shouldn't select "Row" 1001.

In short, the desired resultset should be like:



Row     Number_1    Number_2     REMARKS

1000	 123	       789           Select
1001     789           123           Don't select, as set exists
1002	 234	       678           Select
1003	 234	       567           Select
1004     678	       234           Don't select, as set exists
1005     567	       234           Don't select, as set exists

Can anyone please suggest how can I achieve this? The original resultset is pretty huge - greater than 100,000.

Besides, I can't use any Stored Procedures, but any number of views and sub-views. Any help or suggestion is highly appreciated!

Cheers.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-28 : 06:42:03

Very simple:


SELECT *
FROM table_name T1
WHERE NOT EXISTS
	  (SELECT *
	     FROM table_name T2
	    WHERE Row < T1.ROW
	      AND ((Number_1 = T1.Number_1 AND Number_2 = T1.Number_2)
		        OR (Number_1 = T1.Number_2 AND Number_2 = T1.Number_1)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources