MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (
    productId CHAR(9),
    title VARCHAR(255),
    price DECIMAL(10,2),
    isAvailable BOOLEAN
);
INSERT INTO test VALUES
('ABC123DEF',	'Pink Apple',	0.47,	1),
('ABC123DEF',	'Pink Apple',	0.42,	1),
('ABC123DEF',	'Pink Apple',	0.41,	1),
('ABC333FHG',	'Pink Pear',	0.41,	1),
('ABC333FHG',	'Pink Pear',	0.42,	1),
('ABC333FHG',	'Pink Pear',	0.43,	1),
('FH5845FJG',	'Pink Chocolate',	0.41,	1),
('FH5845FJG',	'Pink Chocolate',	0.45,	1),
('FH5845FJG',	'Pink Chocolate',	0.41,	1);
SELECT * FROM test;


productId
title
price
isAvailable


ABC123DEF
Pink Apple
0.47
1

ABC123DEF
Pink Apple
0.42
1

ABC123DEF
Pink Apple
0.41
1

ABC333FHG
Pink Pear
0.41
1

ABC333FHG
Pink Pear
0.42
1

ABC333FHG
Pink Pear
0.43
1

FH5845FJG
Pink Chocolate
0.41
1

FH5845FJG
Pink Chocolate
0.45
1

FH5845FJG
Pink Chocolate
0.41
1


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;
SELECT * FROM test;


productId
title
price
isAvailable


ABC123DEF
Pink Apple
0.47
1

ABC333FHG
Pink Pear
0.43
1

FH5845FJG
Pink Chocolate
0.45
1

productId	title	price	isAvailable
ABC123DEF	Pink Apple	0.47	1
ABC123DEF	Pink Apple	0.42	1
ABC123DEF	Pink Apple	0.41	1
ABC333FHG	Pink Pear	0.41	1
ABC333FHG	Pink Pear	0.42	1
ABC333FHG	Pink Pear	0.43	1
FH5845FJG	Pink Chocolate	0.41	1
FH5845FJG	Pink Chocolate	0.45	1
FH5845FJG	Pink Chocolate	0.41	1