Post reply

Display duplicate rows

  • 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 OPFA 2

    3166422E 100 OPFA 3

    3068935B 100 OPFA 2

    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

  • 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')

  • 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'

  • 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!

  • November 7, 2008 at 8:06 am

    #895571

    thanks. It works now.

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

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