I have a query as follows:
SELECT * FROM
(SELECT * FROM persons ORDER BY date DESC) AS p
GROUP BY first_name,last_name,work_phone
If you hadn't figured it out already, this removes entries with duplicate names and work phone numbers, leaving only the most recent. There is another field in the person table you should know about, a binary field called DELETED.
The problem is, if there is a duplicate of this nature, I don't want a row to be considered if its DELETED value is TRUE regardless of how recent its date value is. However, if a row has no duplicates it should be included in the results no matter what DELETED value it has.
If duplicates exist there is never a case where all duplicates have DELETED = TRUE, at least one will not be deleted.
See Question&Answers more detail:os