MSSQL

select w.id, wp.age, w.coins_needed, w.power from
wands w inner join (
    select code, power, min(coins_needed) as min_coins from wands
    group by code, power) as mc
    on w.code = mc.code
inner join wands_property wp on w.code = wp.code
where wp.is_evil = 0
and
w.coins_needed = mc.min_coins
order by mc.power desc, wp.age desc

My SQL Query:

This will work.

select w.id,wp.age,w.coins_needed,w.power from wands w left join wands_property wp on w.code=wp.code where wp.is_evil=0 and w.coins_needed IN (select Min(w1.coins_needed) from wands w1 left join wands_property wp1 on w1.code=wp1.code where wp1.is_evil=0 and wp1.age=wp.age and w1.power=w.power) order by w.power DESC,wp.age DESC

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.