Group by and minimum aggregator (with join) in sql

I am trying to teach my sql and am doing the hackerrank practice questions. I came across the Olivander's Inventory question, which says the following: Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand. Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age. Here is the link for more details. It is unhappy with my solution, and I think it is particularly something to do with the group by. What is wrong with what I have done and how do I fix it? Can I same small modifications to make it correct (i.e. still using the group by clause)?

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

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

I would appreciate suggestions! Thanks!

Solution

It's unhappy with w.id in conjunction with that group by clause. Basically, there can be multiple w.id's corresponding to the same w.power, wp.age pairing. You know that what you want is the ID corresponding to the entry for the min, but that's not (directly) legal SQL. What would you have it do if you had put avg instead of min?

Instead, you'll want to use a subquery. Break the problem up into two parts:

Determine what the minimum number of coins is to acquire a wand of a given power and age (I used code, because they're one-to-one mapped, and it avoids a join)
Determine which ID corresponds to that wand
Do the filtering and ordering on the resulting minimum-priced subset

In other words (using JOIN, because it's generally preferred),

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