how to find out duplicate entries?

• Vijay-251247

  Say Hey Kid

  Points: 684

  More actions

  January 31, 2006 at 3:17 am

  #90526

  Hi

  How do i find out if there are any duplicate entries in a table?

  Thanks in advance

  Vij

   

• Jan-155192

  SSCrazy

  Points: 2613

  More actions

  January 31, 2006 at 3:32 am

  #617899

  You could do something like this:

  SELECT field

  FROM table

  GROUP BY field

  HAVING COUNT(field) > 1

• Scott Beckstead

  Right there with Babe

  Points: 780

  More actions

  February 1, 2006 at 11:09 am

  #618181

  select column1, column2,column3,column4,column5,column6 from tblone where column1 in

  (select column1 from tblone

  group by column1

  having count(column1) > 1)

  and

  column5 in (select column5 from tblone

  group by column5

  having count(column5) > 1)

  order by column1, column2 asc

  In my case if an entry has a different column5 it doesn't count as a duplicate.  Please note that this is a brute force method and not necessarily the most efficient. 

   

  ---

  Kindest Regards,

  Scott Beckstead

  "We cannot defend freedom abroad by abandoning it here at home!"
  Edward R. Murrow

  scottbeckstead.com

• Sorin Petcu

  Hall of Fame

  Points: 3195

  More actions

  February 7, 2006 at 7:59 am

  #619290

  another solution:

   

  SELECT     field, COUNT(*) AS cnt

  FROM         dbo.table

  GROUP BY field

  HAVING      (COUNT(*) > 1)

   

  this will displays the field duplicated and how many is duplicated.

  In Theory, theory and practice are the same...In practice, they are not.