SELECT WA.ID, WP.AGE, WA.COINS_NEEDED, WA.POWER FROM WANDS WA INNER JOIN WANDS_PROPERTY WP ON WA.CODE = WP.CODE WHERE WP.IS_EVIL = 0 AND WA.COINS_NEEDED = (SELECT MIN(COINS_NEEDED) FROM WANDS WHERE CODE = WA.CODE AND POWER = WA.POWER AND WP.IS_EVIL = 0) ORDER BY WA.POWER DESC, WP.AGE DESC;

select id, age, coins_needed,power from Wands inner join Wands_Property on Wands_Property.code = Wands.code where is_evil=0 and coins_needed =( SELECT MIN(w1.coins_needed) FROM Wands w1 JOIN Wands_Property wp1 ON w1.code = wp1.code WHERE wp1.is_evil = 0 AND w1.power = wands.power AND wp1.age = Wands_Property.age) order by power desc,age desc

MYSQL Solution: The concept is to create a Sub query to find the min of Coins_needed and Join all the tables together. SELECT id, age, Coins_needed, w1.power FROM Wands w1 JOIN Wands_property wp1 ON w1.code = wp1.code JOIN (SELECT w.code, power, min(w.coins_needed) AS Min_coins FROM Wands w JOIN Wands_property wp ON w.code = wp.code WHERE wp.is_evil = 0 GROUP BY w.code, w.power) AS CTE ON CTE.code = w1.code AND CTE.power = w1.power AND CTE.Min_coins = w1.Coins_needed ORDER BY w1.power DESC, age DESC

MySQL solution:

select a.id, a.power, b.age, a.coins_needed from wands a join wands_property b on b.code = a.code where b.is_evil = 0 and a.coins_needed = (select min(a1.coins_needed) from wands a1 join wands_property b1 on a1.code = b1.code where b.age = b1.age and a.power = a1.power) order by a.power desc, b.age desc;

Nowhere in the question it is mentioned to only return the min record per group of age and power. Question is pooly written.