You are viewing a single comment's thread. Return to all comments →
Hi do you know why it doesn't work if I change the line
where w1.power = w.power and p1.age = p.age
group by w1,power, p1.age
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
coins_needed = any(select min(coins_needed) from Wands as w1 join Wands_Property as p1 on (w1.code = p1.code) group by age, power)
"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.
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