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.
I've compared my result to that of @dung_zhang2012
I understand why theirs works. And I see my provides a slightly different result. I just don't understand how mine is so different. Clarification would really help a lot.
difference is your code produces many number of rows for same age.I don't know why? we have to produce only those rows which have minimum_need for each age
It will not be able to distinguish between price when we have minimum price set and equal power and age you have to use a with clause or select statement within select.
Looks correct, but I don't think it compiles. When I run your query in MySQL I get
ERROR1055(42000)atline4:Expression#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_4m7xguxkire.WANDS.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
select
w.id, wp.age, w.coins_needed, w.power
from
WANDS_PROPERTY as wp JOIN WANDS as w on w.code = wp.code,
(SELECT
age, min(any_value(coins_needed)) coins_needed, power
FROM
WANDS_PROPERTY JOIN WANDS on WANDS.code = WANDS_property.code
WHERE
is_evil = 0
GROUP BY
age, power)t
where
wp.age = t.age and w.coins_needed = t.coins_needed and w.power = t.power
ORDER BY
t.power DESC, t.age DESC;
first of all you need to add group by id as well. having that,
it will make groups of single rows due to id being unique those making having statement useless.
thats what i think.
Ollivander's Inventory
You are viewing a single comment's thread. Return to all comments →
Can't figure out where i'm going wrong.. I've checked for duplicate rows and minimums... Am I missing something? I've been stuck on this for 3 days.
I've compared my result to that of @dung_zhang2012 I understand why theirs works. And I see my provides a slightly different result. I just don't understand how mine is so different. Clarification would really help a lot.
Thank you! --Krista
have you figured out why?
It will not be able to distinguish between price when we have minimum price set and equal power and age you have to use a with clause or select statement within select.
Have you figured out why? I have the same question.
puzzles me too.
first of all, know how to use group by. you can only use the columns that are grouping or the aggregate function in the select statement.
Looks correct, but I don't think it compiles. When I run your query in MySQL I get
and in SQL server I get
select w.id, wp.age, w.coins_needed, w.power from WANDS_PROPERTY as wp JOIN WANDS as w on w.code = wp.code, (SELECT age, min(any_value(coins_needed)) coins_needed, power FROM WANDS_PROPERTY JOIN WANDS on WANDS.code = WANDS_property.code WHERE is_evil = 0 GROUP BY
age, power)t where wp.age = t.age and w.coins_needed = t.coins_needed and w.power = t.power ORDER BY t.power DESC, t.age DESC;
first of all you need to add group by id as well. having that, it will make groups of single rows due to id being unique those making having statement useless. thats what i think.
What do you mean by HAVING MIN(coins_needed)?