You are viewing a single comment's thread. Return to all comments →
I think you're doing the sub-query for every row in the result set though.
If you do like this instead, you only run the sub query once, when performing the join:
select wands.id, min_prices.age, wands.coins_needed, wands.power
inner join (select wands.code, wands.power, min(wands_property.age) as age, min(wands.coins_needed) as min_price
inner join wands_property
on wands.code = wands_property.code
where wands_property.is_evil = 0
group by wands.code, wands.power) min_prices
on wands.code = min_prices.code
and wands.power = min_prices.power
and wands.coins_needed = min_prices.min_price
order by wands.power desc, min_prices.age desc
Agreed! That is what I did as well.
wands.power = min_prices.power
is redundant since you are already grouping it with wands.code The query should work without it.
@ravi: Could you please explain the above query. Thank you.
I understood the query but could not make as to why minimum function is used on the age ? Would be great if someone can explain this point ?
Not sure if I am correct, but he used min() with age just to trick the group by clause, because he need age in the result but not in the group by clause so safely putting it inside an agreegate function. (there is only one age for each code so min(age) won't affect the result) . Hope it helps !!
I do agree with you. Here min(age) is not needed. We could add age to group by and remove min(age) instead since there is one-on-one correspondence between age and code.
would you please tell me why min(wands_property.age) as age? I think for a certain code, the age is fixed.
why do we need a min(age) though? i seem to have been able to get it without this step.
Hi could you explain why you need to calculate min(age)?
I thought the question is for each age and power combination, what is are the lowest prices for those non-evil wands.
When you group the results by a column, you have to explicitly tell how to aggregate all the other columns you are selecting. Even though the ages are all the same inside a group, you have to use min(age) or max(age) or any other aggregation function.