locating rows with duplicate field values

  • HI. I have a sql table that has a field that acts as a key but the field was not set up as key. I know there are duplicate values in that field and I want to be able to display the rows where this particular field has been duplicated. Can someone share some sql with me on how to do this. 

    Thanks,

    Juanita 

     

  • Select key1, key2, count(*) - 1 as Dups from dbo.MyTable group by key1, key2 having count(*) > 1 order by key1, key2

  • THANK YOU SO MUCH !!!! IT WORKS PERFECTLY !!! 

  • Though you have a solution, there's an excellent TechNet article at http://support.microsoft.com/default.aspx?scid=kb;en-us;139444.  I had such a problem at a previous job that I created a multi-step SQL script that I loaded then manually selected and executed each step to do a cleanup.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply