Find duplicate records with a specific value in column

Question

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.

*To pull out the one with the ... ID, I can do* - `no_purchase` value in this case is indefinite. — Akina, Sep 28 '21 at 12:32
do you want to select duplicate records with highest no_purchase ? — Ali Fidanli, Sep 28 '21 at 12:32
@Akina indefinite but in this case it's fine to pick any row when there are multiple max values. — tarako, Sep 28 '21 at 14:29
*but in this case it's fine to pick any row* I believe. But this is a bomb. If the server SQL mode should be changed by any reason your query should fail - do you need in such surprise? Wrap this output column into any aggregate function and make your query synthactically correct. — Akina, Sep 28 '21 at 15:48
@Akina I don't like a bomb. How can I wrap the output column into an aggregate function? — tarako, Sep 29 '21 at 08:12

score 1 · Accepted Answer · answered Sep 28 '21 at 12:34

1

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.

answered Sep 28 '21 at 12:34

Salman A

Agree. I have a more complicated database, just to make it simple ^^; – tarako Sep 28 '21 at 14:03
This solution will get duplicated rows when there are duplicate rows with the same name, address, MAX(no_purchase). Need to add "GROUP BY name, address" after the last ")". – tarako Sep 29 '21 at 14:47

1 Answers1