select id, age, coins_needed, power from ( select w.id id, wp.age age, w.coins_needed coins_needed, w.power power, dense_rank() over (partition by wp.age,w.power order by w.power desc, w.coins_needed) dr from wands w join (select * from wands_property where is_evil=0) wp on w.code=wp.code) where dr=1 order by power desc, age desc;

Why below code is throwing an error?? SELECT id, age, coins_needed, power FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY w.power, wp.age ORDER BY w.coins_needed) AS rn, w.id AS id, wp.age AS age, w.coins_needed AS coins_needed, w.power AS power FROM Wands w JOIN Wands_Property wp ON w.code = wp.code WHERE wp.is_evil=0)t WHERE rn=1 ORDER BY power DESC, age DESC;

mssql select id, age, coins_needed, power from ( select w.id, wp.age, w.coins_needed, w.power, rank() over(partition by w.power, wp.age order by w.coins_needed) Rank from Wands w join Wands_Property wp on w.code = wp.code where wp.is_evil = 0 ) t where Rank = 1 order by power desc, age desc, coins_needed;

SELECT
w.id,
wpro.age, 
w.coins_needed,
w.power
FROM Wands as w
JOIN Wands_Property as wpro
ON w.code = wpro.code
WHERE
wpro.is_evil = 0
AND
w.coins_needed = (
SELECT Min(w2.coins_needed)
FROM Wands as w2
JOIN Wands_Property as wpro2
ON w2.code = wpro2.code
WHERE wpro2.is_evil = 0 AND wpro.age = wpro2.age AND w.power = w2.power
)
ORDER BY
w.power DESC,
wpro.age DESC;

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