select A.id, B.age, A.coins_needed, A.power FROM Wands A JOIN Wands_Property B ON A.code = B.Code JOIN ( SELECT C.code, C.power , MIN(C.coins_needed) AS coins FROM Wands C JOIN Wands_Property D ON C.code = D.code WHERE D.is_evil = 0 Group By D.age, C.power ) AS filtered ON A.code = filtered.code AND A.coins_needed = filtered.coins AND A.power = filtered.power Order By A.power DESC, B.age DESC, A.coins_needed ASC

select A.id, B.age, A.coins_needed, A.power FROM Wands A JOIN Wands_Property B ON A.code = B.Code JOIN ( SELECT C.code, C.power , MIN(C.coins_needed) AS coins FROM Wands C Group By C.code, C.power ) AS filtered ON A.code = filtered.code AND A.coins_needed = filtered.coins Where B.is_evil = 0 Order By A.power DESC, B.age DESC, A.coins_needed ASC

MySQL Query:

    (select w.id 
     from
        wands w
        inner join
        wands_property wt
        on w.code = wt.code
     where w.power = wa.power 
        and wt.age = wp.age 
        and w.coins_needed = min(wa.coins_needed)
     limit 1
    ) wand_id,
    wp.age wand_age,
    min(wa.coins_needed) wand_price,
    wa.power wand_power
from
    wands wa
    inner join
    wands_property wp
    on wa.code = wp.code
where wp.is_evil = 0
group by wa.power, wp.age
order by wa.power desc, wp.age desc;

Exaplanation:

This query worked in MySQL because MySQL is lenient and allows the use of aggregate functions like MIN() inside the WHERE clause of a subquery, even though this is not strictly valid in standard SQL. In proper ANSI SQL, aggregate functions can only appear in the SELECT or HAVING clauses or within subqueries that include a GROUP BY. MySQL implicitly treats the MIN() as an aggregation over the entire subquery, which is why it doesn't throw an error, but this behavior is not portable to stricter SQL databases like PostgreSQL, SQL Server, or Oracle, where such a query would fail. To ensure strict correctness and portability, the subquery should either use GROUP BY to compute the minimum or, more efficiently, apply window functions like ROW_NUMBER() to select the minimum value per group. So while your logic and result were correct for the immediate context, the technical application of MIN() in the WHERE clause is not universally valid SQL.

mySQL - this one was confusing to me but doing it like this seemed to make the most sense to me:

SELECT w.id, wp.age, w.coins_needed, w.power
FROM Wands w
JOIN Wands_Property wp ON w.code = wp.code
JOIN (
    SELECT wp.age, w.power, MIN(w.coins_needed) AS min_cost
    FROM Wands w
    JOIN Wands_Property wp ON w.code = wp.code
    WHERE wp.is_evil = 0
    GROUP BY wp.age, w.power
) AS best ON wp.age = best.age 
         AND w.power = best.power 
         AND w.coins_needed = best.min_cost
WHERE wp.is_evil = 0
ORDER BY w.power DESC, wp.age DESC;

SQL Server Code:-

WITH cte
AS (
    SELECT 
        w.id AS Id, 
        wp.age, 
        w.coins_needed, 
        w.[power],
        ROW_NUMBER() OVER(
            PARTITION BY w.[power], wp.age
            ORDER BY coins_needed, id
        ) AS rn
    FROM Wands w
    JOIN Wands_Property wp
    ON wp.code = w.code
    WHERE wp.is_evil = 0
    -- ORDER BY 
    --     w.[power] DESC,
    --     wp.age DESC
)
SELECT Id, age, coins_needed, [power]
FROM cte 
WHERE rn = 1
ORDER BY 
    [power] DESC,
    age DESC