467,915 Members | 1,265 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

Delete duplicates in each column, not duplicate records

watertraveller
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
  • viewed: 3795
Share:
1 Reply
ck9663
Expert 2GB
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.

Similar topics

3 posts views Thread by Alexander Anderson | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.