Displaying duplicate entries.

• Tsql-561486

  SSC Eights!

  Points: 821

  More actions

  February 13, 2008 at 3:37 am

  #71568

  Hi,

  Please let me know how to find out or display only the duplicate entries in table.

  Thanks in advance

• martin.thorin

  Old Hand

  Points: 327

  More actions

  February 13, 2008 at 3:45 am

  #777734

  Hi

  To find duplicate ( or even more than duplicates ) try this

  select

  field1,

  field2,

  count(*)

  from table

  group by

  field1,

  field2

  having count(*)>1

  where field1, field2 are the fields that will be checked for duplicates.

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9119

  More actions

  February 17, 2008 at 2:49 pm

  #779210

  Hi,

  in Nutshell use the below query to find out the duplicate records.

  SELECT

  list_of_all_columns

  FROM

  tablename

  GROUP BY

  list_of_all_columns

  HAVING

  count(*) > 1

  Thanks -- Vj

  http://dotnetvj.blogspot.com

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers

  
  

• naseemnaz2

  SSC Enthusiast

  Points: 176

  More actions

  September 7, 2009 at 10:52 pm

  #1050297

  Create table tb_MyTest (n_RecordID int identity(1,1) , s_Name Varchar(20) )

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Shadique'

  Insert into tb_MyTest(s_Name) Select 'Jamil'

  Insert into tb_MyTest(s_Name) Select 'Begum Kubra'

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Begum Kubra'

  Insert into tb_MyTest(s_Name) Select 'Shakeel'

  /*Find Duplicate Reports*/

  Select count(1), s_Name from tb_MyTest

  group by s_Name

  Having Count(1)>1

  /*Another Way to find Duplicate Rerords in Table*/

  Select s_Name

  FROM tb_MyTest

  WHERE n_RecordID NOT IN

  (

  SELECT MAX(n_RecordID)

  FROM tb_MyTest

  GROUP BY s_Name

  )

  Naseem Akhtar

• naseemnaz2

  SSC Enthusiast

  Points: 176

  More actions

  September 7, 2009 at 10:56 pm

  #1050298

  Create table tb_MyTest (n_RecordID int identity(1,1) , s_Name Varchar(20) )

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Shadique'

  Insert into tb_MyTest(s_Name) Select 'Jamil'

  Insert into tb_MyTest(s_Name) Select 'Begum Kubra'

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Begum Kubra'

  Insert into tb_MyTest(s_Name) Select 'Shakeel'

  /*Find Duplicate Reports*/

  Select count(1), s_Name from tb_MyTest

  group by s_Name

  Having Count(1)>1

  /*Another Way to find Duplicate Rerords in Table*/

  Select s_Name

  FROM tb_MyTest

  WHERE n_RecordID NOT IN

  (

  SELECT MAX(n_RecordID)

  FROM tb_MyTest

  GROUP BY s_Name

  )

  Naseem Akhtar

• naseemnaz2

  SSC Enthusiast

  Points: 176

  More actions

  September 7, 2009 at 11:00 pm

  #1050299

  Create table tb_MyTest (n_RecordID int identity(1,1) , s_Name Varchar(20) )

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Shadique'

  Insert into tb_MyTest(s_Name) Select 'Jamil'

  Insert into tb_MyTest(s_Name) Select 'Begum Kubra'

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Namha'

  Insert into tb_MyTest(s_Name) Select 'Begum Kubra'

  Insert into tb_MyTest(s_Name) Select 'Shakeel'

  /*Find Duplicate Reports*/

  Select count(1), s_Name from tb_MyTest

  group by s_Name

  Having Count(1)>1

  /*Another Way to find Duplicate Rerords in Table*/

  Select s_Name

  FROM tb_MyTest

  WHERE n_RecordID NOT IN

  (

  SELECT MAX(n_RecordID)

  FROM tb_MyTest

  GROUP BY s_Name

  )

  Naseem Akhtar