with data as( select w.id as id, wp.age as age , w.coins_needed as cn, w.power as p, row_number() over (partition by age, power order by coins_needed) as min_cn from Wands w inner join Wands_Property wp using(code) where wp.is_evil = 0) select id, age, cn, p from data where min_cn = 1 order by p DESC, age DESC;

SELECT w.id, p.age, w.coins_needed, w.power FROM wands w JOIN wands_property p ON w.code = p.code WHERE p.is_evil = 0 -- minimum coins needed to buy that wand with same power and age AND w.coins_needed = (SELECT MIN(coins_needed) FROM wands w1 JOIN wands_property p1 ON w1.code = p1.code WHERE w1.power = w.power AND p1.age = p.age) ORDER BY w.power DESC, p.age DESC

select id, age, coins_needed, power from ( select id, age, coins_needed, power, rank() over (partition by power, age order by coins_needed) as [rank] from wands w join wands_property wp on w.code = wp.code where is_evil=0) a where rank =1 order by power desc, age desc

Oracle/MySQL

select id, age, coins_needed, power
from (
    select id, age, coins_needed, power,
    rank() over(partition by age, power
                order by coins_needed) rnk
    from (
        select id, coins_needed, power,
              (select age from wands_property
               where code = w.code) age,
              (select is_evil from wands_property
               where code = w.code) is_evil
        from wands w)
    where is_evil = 0)
where rnk = 1
order by power desc, age desc;