SELECT w.id, p.age, w.coins_needed, w.power from wands w join wands_property p on w.code=p.code where p.is_evil=0 and w.coins_needed=( select min(wa.coins_needed) from wands wa join wands_property wp on wa.code=wp.code where wa.power=w.power and wp.age=p.age ) order by w.power desc, p.age desc;

• 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 = (
• 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
• AND wp2.is_evil = 0
• )
• ORDER BY
• w.power DESC,
• wp.age DESC;
*

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

Kinda messed up how easy this is to do in SQLite3. It doesn't prevent you from including id in the output of the aggregation, so I had a local solution with a trivial answer. But ended up scratching my head for a while, trying to get the same results online haha.

/* w -> alias de la tabla Wands wp -> alias de la tabla Wands_Properties w2 -> wp2 -> alias of Wands_Property in subquery c -> Resultado de la Subconsulta */

-- Instrucciones

SELECT w.id, wp.age, w.coins_needed, w.power FROM Wands w

-- JOIN que busca traer propiedades principales a la consulta principal

JOIN Wands_Property wp ON w.code=wp.code -- Usar codigos validos en wp

-- JOIN busca conectar cada varita con su propiedad para poder agrupar por edad y descartar las varitas malignas antes de calcular el minimo.

JOIN ( SELECT w2.power, wp2.age, MIN(w2.coins_needed) c FROM Wands w2 JOIN Wands_Property wp2 ON w2.code=wp2.code WHERE wp2.is_evil=0 GROUP BY w2.power, wp2.age -- Calcular el minimo, agrupando por el mismo poder y edad )

m ON m.power=w.power AND m.age=wp.age AND m.c=w.coins_needed -- Unir la subconsulta con las tablas principales

WHERE wp.is_evil=0 ORDER BY w.power DESC, wp.age DESC; -- Ordenar primero el poder de manera desendente, si hay empate entonces la edad