Problem with only_full_group_by

Question

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?

Answer 1

Telling you directly the answer would be cheating, so I will give you just a hint- the problem is about a very very famous SQL problem, so much, that it has its own page on the MySQL manual:

https://dev.mysql.com/doc/refman/5.7/en/examples.html

And a search finds that this kind of problem has been asked on this site at least a dozen times already.

Telling you more than that would be against the spirit of the challenge.

I can explain why it errors out- When using an aggregated function, you can only select fields you have aggregated by, or other fields, using an aggregated function, but you cannot select non-aggregated-by fields, because SQL doesn't know you want the minimum one. What you want to do is to find the minimum, and then use that value to get the corresponding one on the same row, but you cannot "aggregate and get the value of another column" with such a simple query- once it gets aggregated, the other fields get "lost". In previous versions of MySQL, your query was legal, but it gave out a "random/not deterministic" id value.

Answer 2

you can try enclosing the column (id) inside ANY_VALUE(id) that will ignore this raise condition.

This is because of sql_mode=only_full_group_by. Generally its sql_mode=traditional