CODEWITHSUNDEEP
mysql
tarako
tarako

Reputation: 137

Find duplicate records with a specific value in column

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.

Upvotes: 0

Views: 507

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

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.

Upvotes: 1

Related Questions