Steve Manes

One issue I run across occasionally is a table with duplicate entries such as two entries for the same company in an accounts payable system.  This can create embarrassing problems with billing if ACME Inc #1 is 90 days overdue because someone posted a payment, and now a credit, to ACME Inc #2.

I ran across this problem recently on a health records system which was missing a UNIQUE constraint on a secondary patient ID field: student ID.   The application was originally built without that student ID but  the client decided later that it wanted it added to the patient record.  That was done but without any database-bound constraints because it was an optional field that most clinics probably wouldn't use.  Or so the client thought.

A few months later, the client was so happy with the software that it decided to move all its student records over to the application so that the New Patient flow could import data to a data warehouse as a preload, saving the operator time and errors when the patient was entered into the core system.  The problem was that during those interim months, operators had been manually putting bad data into those student IDs resulting in New Patient importing patients from the warehouse which already existed in the patient table.  Voila, two identical patients with differing student IDs as well as two very different patients with the same student ID.

The only correctlive option in this case is to merge the two patients to one, but before you can do that you have to decide which patient record is the "authority" and what data you want to merge from the other record(s).  And before you can do that you have to identify those duplicates.

This query will accomplish that.  It employs the HAVING filter companion of the GROUP BY result set aggregator.   I don't see many queries employing HAVING, which is a shame because it's a powerful way to filter grouped data, almost like a WHERE clause.

Here's an example of a query on a patient table returning all duplicate patients with the same first and last name and student ID:
 
SELECT
    COUNT(*),
    first_name,
    last_name,
    student_id
FROM patients
GROUP BY first_name, last_name, student_id
HAVING COUNT(*) > 1;
Result:

  count | first_name  |   last_name    | student_id
--------+-------------+----------------+------------
     2  | KENNETH     | RIVERA         | 268098363
     2  | ERIC        | SALLES         | 217257892
     2  | MICHAEL     | LAWRENCE       | 274295927
     2  | LOUISE      | ROSARIO        |
     2  | KENDRA      | PRICE          | 224434348    

You can, of course, add other conditons to that query to find, for instance, all patients with the same last_name and student_id.  But that just gives you a list of the duplicated fields in the table.   Most of these results do indeed look like dupes but in the case of LOUISE ROSARIO, is she a dupe or is she two different patients without a student ID?  You need to eyeball their records to make sure.  What would be nicer is a pointer to the actual records where those dupes exist.  That's a tougher challenge because you can't add patient_id to this query without breaking the GROUP BY logic.

That can be accomplished with a nested, or sub, query with the above query serving us that search data.

SELECT
    PD.person_id,
    PD.first_name,
    PD.last_name,
    PD.dob,
    PD.student_id
FROM patients AS PD,
(SELECT
        first_name,
        last_name,
        student_id
    FROM patients
    GROUP BY first_name, last_name, student_id
    HAVING COUNT(*) > 1) AS SUB1
WHERE
    PD.first_name = SUB1.first_name
AND PD.last_name = SUB1.last_name
AND PD.student_id = SUB1.student_id
ORDER BY PD.student_id, PD.last_name, PD.first_name;
    

Results:

patient_id | first_name |   last_name    |    dob     | student_id
-----------+------------+----------------+------------+-----------
     27914 | VICTOR     | TORREDO        | 1995-02-14 | 432019685
     27915 | VICTOR     | TORREDO        | 1995-02-14 | 432019685
      8191 | ESTHER     | WILLIAMS       | 1995-09-23 | 206567040
      4519 | ESTHER     | WILLIAMS       | 1995-09-23 | 206567040
     17236 | THOMAS     | ASHCROFT       | 1995-02-13 | 333279517
      1288 | THOMAS     | ASHCROFT       | 1995-02-13 | 333279517
     17888 | LOUISE     | ROSARIO        | 1987-06-17 |
     11368 | LOUISE     | ROSARIO        | 1972-01-30 |

Aha, they're obviously two different LOUISE ROSARIOs.

By the way, SQL, being as flexible as it is, would let you write this another way, such as with an explicit JOIN.  I leave that as an exercise to the interested user <g>.
 

A lot of my blog posts are Post-Its for my own brain.  So is this one.  I've managed to accidentally drag my Applications folder to the trash twice now and both times I had to wade through a half dozen non-optimal solutions on the web to find the one that actually restored it to what I had before.

In OSX 10.6 it's actually pretty easy, if not very intuitive.

Open Finder and select Macintosh HD.  Then right-click on Applications in the second column and select Get Info.  That will give you a popup like this.

Under Preview, you'll see a folder with an "A" on it.  Click and drag it just to the right of your Dock.  Because it's a folder OSX won't let you actually place it in the Dock.

Then right click on the folder to set how you would like it displayed, i.e. List, Grid, etc.