SQL query return duplicate with most recent date and more

Question

I have a table: 'person' with many columns. I need to return the full row of every entry that does not have a "duplicate" (defined below) along with the following: find the entries in that table that share the properties: first_name, last_name, and work_phone (these are duplicates for my purposes), and return only the entry with the most recent value in its date_modified field, ignoring the rest.

I feel like this is either a rather advanced query, or is deceptively simple. Either way I can't figure it out. I am using MySQL 5.

Answer 1

Return only entries with the most recent value in its date_modified field, ignoring the rest:

SELECT p.* FROM
  ( SELECT max(date_modified) as most_recent_date 
    FROM person 
    GROUP BY first_name,last_name,work_phone ) p1
JOIN person p
ON p.date_modified = p1.most_recent_date

This will work well assuming date_modified is unique for every combination of fields we are grouping on. If not we have to join on a field that is unique, taking one arbitrary UUID (limit 1) satisfying most recent condition.

SELECT p.* FROM
  ( SELECT *,max(date_modified) as most_recent_date 
    FROM person 
    GROUP BY first_name,last_name,work_phone ) p1
JOIN person p
ON p.UUID =
  ( SELECT p_uniq.UUID 
    FROM person p_uniq
    WHERE p_uniq.first_name = p1.first_name
      AND p_uniq.last_name = p1.last_name
      AND p_uniq.work_phone = p1.work_phone
      AND p_uniq.date_modified = p1.most_recent_date
    LIMIT 1 )

And finally, return the full row of every entry that does not have a "duplicate":

SELECT * , count( * ) AS entries
FROM `person`
GROUP BY first_name, last_name, work_phone
HAVING entries =1