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?