mysql query - how to delete duplicates based on highest int?

Question

I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId.

+----------------------+--------------+------+-----+-------------------+-------------------+
| Field                | Type         | Null | Key | Default           | Extra             |
+----------------------+--------------+------+-----+-------------------+-------------------+
| lastPriceBid         | varchar(125) | YES  |     | NULL              |                   |
| dateBidded           | varchar(125) | YES  |     | NULL              |                   |
| owner                | varchar(65)  | YES  |     | NULL              |                   |
| status               | int(11)      | YES  |     | 0                 |                   |
| userId               | varchar(125) | YES  |     | NULL              |                   |
| siteId               | int(11)      | YES  |     | NULL              |                   |
| lastPriceBid_rounded | int(11)      | YES  |     | NULL              |                   |
| from_nickname        | varchar(125) | YES  |     | NULL              |                   |
| id                   | int(11)      | NO   | PRI | NULL              | auto_increment    |
| time_updated         | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update         |
|                                                                        CURRENT_TIMESTAMP |
+----------------------+--------------+------+-----+-------------------+-------------------+

So far I've tried

DELETE n1 
FROM offers n1, offers n2 
WHERE n1.lastPriceBid_rounded < n2.lastPriceBid_rounded 
AND n1.userId = n2.userId;

but it doesn't seem to work well. I still have some duplicates (I see that when I use SELECT DISTINCT).

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

Why does the DELETE Query not work ?

Whenever a table is used against itself in a query other than SELECT, rows tend to disappear during the optimization phase. I wrote about that back on Feb 22, 2011 : Problem with MySQL subquery

What can you do then ?

Create a new offers table to see what the table would look like if the DELETE were actually done:

DROP TABLE IF EXISTS offers_after_delete;
CREATE TABLE offers_after_delete LIKE offers;
INSERT INTO offers_after_delete
SELECT B.* FROM
(SELECT userId,site,MAX(lastPriceBid_rounded) highest_amount
FROM offers GROUP BY userId,site) A
INNER JOIN offers B USING (userId,site);

Please note that I based the delete operation on a distinct user per unique site. Now, browse through offers_after_delete. If the table looks correct to you, then delete like this:

DROP TABLE IF EXISTS offers_after_delete;
CREATE TABLE offers_after_delete LIKE offers;
INSERT INTO offers_after_delete
SELECT B.* FROM
(SELECT userId,site,MAX(lastPriceBid_rounded) highest_amount
FROM offers GROUP BY userId,site) A
INNER JOIN offers B USING (userId,site);
ALTER TABLE offers RENAME offers_old;
ALTER TABLE offers_after_delete RENAME offers;
DROP TABLE offers_old;

Please rehearse this on a DB Staging Server.

Give it a Try !!!

mysql query - how to delete duplicates based on highest int?

1 Answers1