By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Delete duplicates in each column, not duplicate records

watertraveller
P: 4
Hi all. My ultimate goal is to return two columns, where no single value appears anywhere twice. This means that not only do I want to check that nothing from column A appears in column B and vice-versa, but I also don't want the same value appearing twice in A and twice in B.

So far I have:

Expand|Select|Wrap|Line Numbers
  1. --Diff the columns
  2. INSERT INTO @Table
  3. SELECT One, Two
  4. FROM @Column1 a
  5. FULL OUTER JOIN @Column2 b
  6. ON ((One NOT IN (Two)) OR (Two NOT IN (One)))
  7.  
But then my trouble is removing the redundant values in each column. I tried splitting the table into single-column tables so that they would no longer be linked together, and then deleting duplicate records out of that single-column table. But it didn't work.

Expand|Select|Wrap|Line Numbers
  1. -- Attempt at removing duplicate values
  2. DELETE FROM @Column1
  3. DELETE FROM @Column2
  4. INSERT INTO @Column1 SELECT One FROM @Table
  5. INSERT INTO @Column2 SELECT Two FROM @Table
  6. INSERT INTO @Column3 SELECT DISTINCT One FROM @Column1 
  7.          ORDER BY One
  8. INSERT INTO @Column4 SELECT DISTINCT Two FROM @Column2 
  9.          ORDER BY Two
  10.  
But when I display these two columns together there are duplicates, and when I display them individually they aren't even in order. What's wrong here?

Thanks,
-C
Oct 17 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
Could you post some of your sample data? Let's see if we can find a pattern among the values.

-- CK
Oct 17 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.