one example:

WITH
cte AS ( 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY productId ORDER BY price DESC) rn
    FROM test
)
DELETE test
FROM test
NATURAL JOIN cte
WHERE cte.rn > 1;

The query saves the highest price row for each product ID and deletes the other rows for that product.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=40df8e8e4b3eb206e0f73b7ce3a70a a5一个>

NOTE - Every complete row that must be stored is unique (rows that must be deleted may have complete duplicates).

WITH
cte AS ( 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY productId ORDER BY price DESC) rn
    FROM test
)
SELECT *
FROM cte
WHERE rn = 1;

This query does not require the rows to be unique, in which case only a single copy of the row is returned. If you need all copies, use RANK() or DENSE_RANK() instead of ROW_NUMBER().

Solution for MySQL 5.x version.

SELECT *
FROM test
WHERE NOT EXISTS ( 
    SELECT NULL
    FROM test t
    WHERE test.productId = t.productId
      AND test.price 
This query will return all replicas if they exist. If you only need one copy, add DISTINCT.