Geek Answers Handbook

Return duplicate SQL query with latest date and more

I have a table: "man" with many columns. I need to return the full row of each entry that does not have "duplicates" (defined below), as well as the following: find the entries in this table that share the properties: first_name, last_name and work_phone (these are duplicates for my purpose) and return only the record with the most recent value in the date_modified field, ignoring the rest.

I feel this is either a fairly advanced request, or deceptively simple. In any case, I cannot figure it out. I am using MySQL 5.

+5
source share
1 answer

Returns only records with the most recent value in the 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

, date_modified , . , , UUID ( 1), .

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 )

, , , "":

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

More articles:


All Articles