We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Loading...
  1. Practice
  2. SQL
  3. Basic Join
  4. Ollivander's Inventory
  5. Discussions

Ollivander's Inventory

  • decimal_simplex + 5 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
from wands
inner join (select wands.code, wands.power, min(wands_property.age) as age, min(wands.coins_needed) as min_price
            from wands
            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
    • LinVenus + 0 comments

      Agreed! That is what I did as well.

    • RaviPushkar + 1 comment

      I think 

      wands.power = min_prices.power

      is redundant since you are already grouping it with wands.code The query should work without it.

      • LearnCrazy + 1 comment

        @ravi: Could you please explain the above query. Thank you.

        • LearnCrazy + 1 comment

          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 ?

          Thank you!

          • frozendamn + 1 comment

            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 !!

            • wm3wy + 0 comments

              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.

    • zhangzz2015 + 0 comments

      would you please tell me why min(wands_property.age) as age? I think for a certain code, the age is fixed. thank you!

    • anandrajt + 0 comments

      why do we need a min(age) though? i seem to have been able to get it without this step.

    • ArielJiang + 1 comment

      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.

      • victoreufrasio + 0 comments

        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.