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