with cte as ( select w.ID ,wp.age,w.coins_needed,w.power,ROW_NUMBER() over (partition by w.power,wp.age order by w.power desc,wp.age desc) as t from wands w join wands_property wp on w.code=wp.code where wp.is_evil != 1 ) select ID,age,coins_needed,power from cte where t=1 order by power desc,age desc,coins_needed asc

The following worked for me: SELECT W.id, WP.age, W.coins_needed, W.power FROM Wands W INNER JOIN Wands_Property WP ON W.code = WP.code WHERE WP.is_evil != 1 AND W.coins_needed = (SELECT MIN(W2.coins_needed) FROM Wands W2 INNER 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

PLEASE UPVOTE!!!

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(w1.coins_needed) FROM Wands w1 JOIN Wands_Property wp1 ON w1.code = wp1.code WHERE w1.power = w.power AND wp1.age = wp.age AND wp1.is_evil = 0 ) ORDER BY w.power DESC, wp.age DESC;

WITH HP as (SELECT W.id as ID,WP.age as AGE,W.coins_needed as COINS_NEEDED,W.power as POWER, ROW_NUMBER() OVER(PARTITION BY WP.age, W.power ORDER BY W.coins_needed) as RANKER
FROM Wands as W INNER JOIN Wands_Property as WP ON W.code = WP.code WHERE WP.is_evil = 0 ORDER BY W.power DESC, WP.age DESC)

SELECT HP.ID,HP.AGE,HP.COINS_NEEDED,HP.POWER FROM HP WHERE HP.RANKER = 1 ;

i always get this error and i don't know how to fix it "ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cte as "

these are the 2 queries i tried with cte as ( select a.id as id, b.age as age, a.coins_needed as coins_needed, a.power as power, rank() over (partition by age, power order by coins_needed) as rank1 from wands as a left join wands_property as b on a.code = b.code where b.is_evil = 0 ) select id, age, coins_needed, power from cte where rank1 = 1 order by power desc, age desc;

\\\\\\\\\

select id, age, power ,coins_needed from (select w.id, wp.age, w.power,w.coins_needed, Row_number()over(partition by w.power,wp.age order by w.coins_needed) as rn from wands as w inner join Wands_property as wp on w.code = wp.code and wp.is_evil = 0 order by w.power desc, wp.age desc) rr where rn =1 order by power desc, age desc ;