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.
select w.id, p.age, w.coins_needed, w.power
from Wands as w join Wands_Property as p on (w.code = p.code)
where p.is_evil = 0 and
w.coins_needed = (select min(coins_needed) from Wands as w1 join Wands_Property as p1 on (w1.code = p1.code) where w1.power = w.power and p1.age = p.age)
order by w.power desc, p.age desc
BASICALLY THE QUERY IS TO WRITE ALL THE AVAILABLE OPTIONS FOR RON... SO THE LATER SUBQUERY PART SHOULDNT BE COMING IN THIS CONTEXT... SO TECHNICALLY YOU SEE THERE IS SOME MISTAKE WITH THE QUESTIONS
If you use group by , the select( min(coins_needed) ....query will return multiple rows and not a single value.
So, we can't write a condition such as : where coins_needed=select(min(coins_needed) ... group by...) because of multiple rows problem.
You use this line to only get the rows that have the same Power and Age. From these rows that have the same Power and Age you are able to get the minimun coins needed for that group.
you can achieve this by using group by too but then it produces multiple rows. so can't we use 'in' instead of '=' after where to detect these multiple rows
You are grouping all the rows(in subtable) that matches particularly given power, age from base table, then you take minimum coins of the group. Now you repeat for next particular power, age.
I struggled with this too. And I didn't understand the other people's explinations. But, I worked it out my self finally! If you don't include this you'll get the minimum for all of the rows! But including it, gets the minimum for groups that match in both power and age. It does seem like there should be a more intuitive way of doing this, such as usinging group by.
Thank you for the explanation. I think the description of the question let us confused. It is not hard, but the confusion of the question description consumes time.
And in MySQL too, this one has 1 JOIN instead of 2:
SELECT id, age, TT.mincoin, WANDS.power FROM WANDS
JOIN
(SELECT WANDS.code, age, WANDS.power, MIN(coins_needed) as mincoin FROM WANDS
JOIN WANDS_PROPERTY on WANDS.code = WANDS_PROPERTY.code
WHERE is_EVIL = 0
GROUP BY power, age) as TT
on TT.code = WANDS.code
WHERE TT.mincoin = WANDS.Coins_needed
ORDER BY power DESC, age DESC;
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.
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.
especially the subquery: "select min(coins_needed) from Wands as w1 join Wands_Property as p1 on (w1.code = p1.code) where w1.power = w.power and p1.age = p.age".
WHY using "where w1.power = w.power and p1.age = p.age"?
Is this subquery retrieving the minimum price for each combo of "power" and "age"? if so, how is it doing it?
like, WHY is it referencing the values from outer query (for example, p.age and w.power do not belong to this subquery)?
select w.id, p.age, w.coins_needed, w.power from wands w
join wands_property p on w.code=p.code where p.is_evil=0 and w.coins_needed=(select min(w2.coins_needed) from wands w2 where w.code=w2.code and w.power=w2.power) order by w.power desc, p.age desc;
In subquery there is no need to join wands with wands_property because one code = one age (you can just compare codes instead of ages).
I get why we are linking back to the main query from the internal (w1.power = w.power AND p1.age = p.age), but isn't that just to ensure we do not return a minimum that only applies to dark art wands? Why can't we just say p1.is_evil = 0, again? I know this does not work, but I am not sure why.
Ollivander's Inventory
You are viewing a single comment's thread. Return to all comments →
worked in mysql
select w.id, p.age, w.coins_needed, w.power from Wands as w join Wands_Property as p on (w.code = p.code) where p.is_evil = 0 and w.coins_needed = (select min(coins_needed) from Wands as w1 join Wands_Property as p1 on (w1.code = p1.code) where w1.power = w.power and p1.age = p.age) order by w.power desc, p.age desc
Good job
BASICALLY THE QUERY IS TO WRITE ALL THE AVAILABLE OPTIONS FOR RON... SO THE LATER SUBQUERY PART SHOULDNT BE COMING IN THIS CONTEXT... SO TECHNICALLY YOU SEE THERE IS SOME MISTAKE WITH THE QUESTIONS
I agree, the instructions are not explicit in asking us to do a subquery to find the minimum coins needed.
nicely done
Hi do you know why it doesn't work if I change the line
to
Appreciate it!
If you use group by , the select( min(coins_needed) ....query will return multiple rows and not a single value. So, we can't write a condition such as : where coins_needed=select(min(coins_needed) ... group by...) because of multiple rows problem.
Thank you for your explanation. This is really clear and helpful!
Why I cannot use the subquery like this
"any" returns true or false. You can't use it to compare it to "coins_needed".
Here, you just need to remove the "any" and add a where clause to match the correct age and power.
I am confused as to why we need this line:
Could someone please explain it's function?
^WSS can anyone explain the need of the above statement?
I don't understand this part too, why we have to match two tables that are exactly the same?
You use this line to only get the rows that have the same Power and Age. From these rows that have the same Power and Age you are able to get the minimun coins needed for that group.
you can achieve this by using group by too but then it produces multiple rows. so can't we use 'in' instead of '=' after where to detect these multiple rows
You are grouping all the rows(in subtable) that matches particularly given power, age from base table, then you take minimum coins of the group. Now you repeat for next particular power, age.
I struggled with this too. And I didn't understand the other people's explinations. But, I worked it out my self finally! If you don't include this you'll get the minimum for all of the rows! But including it, gets the minimum for groups that match in both power and age. It does seem like there should be a more intuitive way of doing this, such as usinging group by.
Thank you for the explanation. I think the description of the question let us confused. It is not hard, but the confusion of the question description consumes time.
By doing:
group by w1.power, p1.age
You are only considering one direction of the combination (power, age), You would have toconsider the other direction as well, like below:
group by w1.power, p1.age or p1.age, w1.power
And in MySQL too, this one has 1 JOIN instead of 2:
SELECT id, age, TT.mincoin, WANDS.power FROM WANDS JOIN (SELECT WANDS.code, age, WANDS.power, MIN(coins_needed) as mincoin FROM WANDS JOIN WANDS_PROPERTY on WANDS.code = WANDS_PROPERTY.code WHERE is_EVIL = 0 GROUP BY power, age) as TT on TT.code = WANDS.code WHERE TT.mincoin = WANDS.Coins_needed ORDER BY power DESC, age DESC;
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:
Agreed! That is what I did as well.
I think
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 ?
Thank you!
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. thank you!
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.
I am not understanding this solution! >__<
especially the subquery: "select min(coins_needed) from Wands as w1 join Wands_Property as p1 on (w1.code = p1.code) where w1.power = w.power and p1.age = p.age".
WHY using "where w1.power = w.power and p1.age = p.age"?
Is this subquery retrieving the minimum price for each combo of "power" and "age"? if so, how is it doing it?
like, WHY is it referencing the values from outer query (for example, p.age and w.power do not belong to this subquery)?
I had to read this to understand the question properly.
Is poorly worded as they are looking to find a wand with the cheapest price for the given properties.
Unless each wand with the same properties is considered the same wand - but then why would they have their own ids. blah.
I dont undrstand at all what it means..... could you please explain more
Serioulsy, poorly defined problem
i guess it
from http://stackoverflow.com/questions/20074562/group-by-without-aggregate-function
The condition
is not needed for the mapping between code and age is one-one
hey bruh can u clearly explain your answer?
select w.id, p.age, w.coins_needed, w.power from wands w join wands_property p on w.code=p.code where p.is_evil=0 and w.coins_needed=(select min(w2.coins_needed) from wands w2 where w.code=w2.code and w.power=w2.power) order by w.power desc, p.age desc;
In subquery there is no need to join wands with wands_property because one code = one age (you can just compare codes instead of ages).
I get why we are linking back to the main query from the internal (w1.power = w.power AND p1.age = p.age), but isn't that just to ensure we do not return a minimum that only applies to dark art wands? Why can't we just say p1.is_evil = 0, again? I know this does not work, but I am not sure why.
Why doesn't the min coin selection subquery need to check whether or not evil is 0? What if the min(coins_needed) is from a wand which is evil?