I was trying to solve this HackerRank problem using MySQL and here's what I came up with:
select id, wands_property.age, min(wands.coins_needed), wands.power
from wands
join wands_property
on wands_property.code = wands.code
where wands_property.is_evil = 0
group by power, age
order by power desc, age desc;
This gives me a compilation error:
ERROR 1055 (42000) at line 1: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_ctaqjwhj9bt.wands.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Now, I understand that there are multiple results for id - this was the point of the problem, having multiple wands with the same age and power but different prices - but since I've specified that I want the minimum price shouldn't I get only the id corresponding to the wand with the minimum price?
Edit: I solved it with this:
select w.id, wp.age, w.coins_needed, w.power
from wands as w
join wands_property as wp
on wp.code = w.code
where wp.is_evil = 0 and w.coins_needed = (
select min(coins_needed)
from wands
join wands_property
on wands.code = wands_property.code
where wands.power = w.power and wands_property.age = wp.age
group by power, age
)
order by power desc, age desc;
but I noticed something I don't quite understand: if I add
and wands.id = w.id
to the last where clause I'm getting duplicate (power and age) results with different prices instead of only the minimum one. Why is that?