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.
SELECT id, age, coins_needed, power
FROM WANDS_PROPERTY JOIN WANDS on WANDS.code = WANDS_property.code
WHERE is_evil = 0
GROUP BY age, power
ORDER BY power DESC, age DESC;
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.
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
ERROR 1055 (42000) at line 4: 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
and in SQL server I get
An expression of non-boolean type specified in a context where a condition is expected, near 'ORDER'.
w.id, wp.age, w.coins_needed, w.power
WANDS_PROPERTY as wp JOIN WANDS as w on w.code = wp.code,
age, min(any_value(coins_needed)) coins_needed, power
WANDS_PROPERTY JOIN WANDS on WANDS.code = WANDS_property.code
is_evil = 0
wp.age = t.age and w.coins_needed = t.coins_needed and w.power = t.power
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)?