How can I find duplicates in SQL 2008

I need to find duplicate entries of the value 3 in the Status column and delete all rows containg the value of 3? I updated the rows easily but cant figure out how to find the duplicates and get rid of them. Im a rookie. Thanks

Status is my last column.

 17657   0000200133   17714   0000200133   0000200133    3
 17657   0000200133   17714   0000200133   0000200133    1
more ▼

asked Sep 20, 2012 at 09:54 PM in Default

avatar image

20 1 1 2

I don't really get your question. The data above contains two identical rows. Do you want to delete one of them? The Status column is not three in your example data above, that's why I'm a little bit confused.

Sep 21, 2012 at 05:36 AM Magnus Ahlkvist

Sorry. I picked a cloumn that had all duplicates. Lets say the status column has 10 values of the number 1 and 10 values of the number 3. I need to delete all of the values of 3 and leave the values of 1. So if the status column has any values of 3. I want to delete the entire row and leave rows only contaiing the value of 1 in the status column.

Sep 21, 2012 at 11:12 AM Csmiley
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi rookie,

I didnt get your Question clearly. Below query is useful for the remove the duplicate rows except master . I think it will useful for you.

 WITH TempTable AS 
            SELECT   Empno, row_number() OVER ( PARTITION BY Empno ORDER BY Empno) AS ID
                FROM tabEMP
     Delete from TempTable  WHERE  ID > 1
more ▼

answered Sep 21, 2012 at 07:29 AM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 20, 2012 at 09:54 PM

Seen: 591 times

Last Updated: Sep 21, 2012 at 11:13 AM

Copyright 2018 Redgate Software. Privacy Policy