Display duplicate rows

• eseosaoregie

  SSCertifiable

  Points: 6889

  More actions

  November 7, 2008 at 6:57 am

  #73252

  I have written a little script to determine the number of duplicates based on having a certain code.

  The code is as follows.

  SELECT [PatID(HospitalNumber)],

  Specialty_Service,

  POD,

  COUNT(*) as [Number of Dups]

  FROM [5K5_PatLevelData_September_2008-9_MST_20089_029]

  GROUP BY

  [PatID(HospitalNumber)],

  Specialty_Service,

  POD

  HAVING COUNT(*) > 1 AND POD = 'OPFA'

  the output is fine and is as follows

  [PatID(HospitalNumber)], Specialty_Service POD, [Number of Dups]

  3104086X 100 OPFA2

  3166422E 100 OPFA3

  3068935B 100 OPFA2

  However now i would like to produce an output which shows each specific dupe record. So for Example I want a output that shows the rows of the 2 dupes for PatID 3104086X and the 3 rows of dupes for 3166422E

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  November 7, 2008 at 7:13 am

  #895516

  SELECT * FROM [5K5_PatLevelData_September_2008-9_MST_20089_029] WHERE

  [PatID(HospitalNumber)] in (

  SELECT [PatID(HospitalNumber)]

  FROM [5K5_PatLevelData_September_2008-9_MST_20089_029]

  GROUP BY

  [PatID(HospitalNumber)],

  Specialty_Service,

  POD

  HAVING COUNT(*) > 1 AND POD = 'OPFA')

• eseosaoregie

  SSCertifiable

  Points: 6889

  More actions

  November 7, 2008 at 7:45 am

  #895551

  I tried this script and it produces the dupes for other codes. I would like it to produce only the dupe records having POD = 'OPFA'

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 7, 2008 at 7:58 am

  #895564

  the trick is to simply join your original table with the subquery that identified the duplicates;

  try this:

  [font="Courier New"]

  SELECT * FROM

  [5K5_PatLevelData_September_2008-9_MST_20089_029]

  INNER JOIN (

  SELECT  [PatID(HospitalNumber)],

          Specialty_Service,

          POD,

          COUNT(*) AS [Number of Dups]

  FROM [5K5_PatLevelData_September_2008-9_MST_20089_029]

  GROUP BY

          [PatID(HospitalNumber)],

          Specialty_Service,

          POD

  HAVING COUNT(*) > 1 AND POD = 'OPFA'

  )GROUPIE ON  [5K5_PatLevelData_September_2008-9_MST_20089_029].[PatID(HospitalNumber)] = GROUPIE.[PatID(HospitalNumber)]

           AND [5K5_PatLevelData_September_2008-9_MST_20089_029].Specialty_Service = GROUPIE.Specialty_Service

           AND [5K5_PatLevelData_September_2008-9_MST_20089_029].POD =GROUPIE.POD[/font]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• eseosaoregie

  SSCertifiable

  Points: 6889

  More actions

  November 7, 2008 at 8:06 am

  #895571

  thanks. It works now.