mysql query - how to delete duplicates based on highest int?
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 !!!