Find duplicate records with a specific value in column-CodePudding

Similar to this Find duplicate records in MySQL, I want to pull out duplicate records in a MySQL database based on the value of a specific column.

To pull out the one with the highest ID, I can do:

SELECT MAX(id), name, address, no_purchase, count(id) as cnt FROM list
GROUP BY name, address HAVING cnt > 1

To pull out the one with the lowest ID, I can do:

SELECT MIN(id), name, address, no_purchase, count(id) as cnt FROM list
GROUP BY name, address HAVING cnt > 1

How can I pull it based on the highest no_purchase? When I do MAX(no_purchase), I didn't get the correct id.

CodePudding user response：

One generic solution is this:

SELECT *
FROM t
WHERE (name, address, no_purchase) IN (
    SELECT name, address, MAX(no_purchase)
    FROM t
    GROUP BY name, address
)

Having said that, using name and address to identify duplicate people does not always work.