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

  • kshuckerow + 7 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 
HAVING MIN(coins_needed) 
ORDER BY power DESC, age DESC;
    • kshuckerow + 1 comment

      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

      • deepak119 + 1 comment

        have you figured out why?

        • deepak119 + 0 comments
          • 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
          • your code output:-
          • 1303 496 6678 10
          • 1038 496 4789 10
          • 1130 494 9439 10
          • 1315 492 4126 10
          • 892 492 4345 10
          • 9 491 7345 10
          • 1221 483 7985 10
          • 858 483 4352 10
          • 1164 481 9831 10
          • 1288 464 4952 10
          • 861 462 8302 10
          • 412 455 5625 10
          • 996 451 8884 10
          • 327 451 9792 10
          • 1608 446 8351 10
          • 1376 443 1735 10
          • 1330 430 5182 10
          • 1633 425 2206 10
          • 920 425 6008 10
          • 1197 419 3468 10
          • 441 416 2508 10
          • 968 416 6344 10
          • 1134 413 7960 10
          • 981 413 2566 10
          • 424 413 997 10
          • 1298 397 3810 10
          • 635 395 4376 10
          • 1125 395 2299 10
          • 333 393 926 10
          • 1911 393 5467 10
          • 363 388 4477 10
          • ...
          • correct output:-
          • 1038 496 4789 10
          • 1130 494 9439 10
          • 1315 492 4126 10
          • 9 491 7345 10
          • 858 483 4352 10
          • 1164 481 9831 10
          • 1288 464 4952 10
          • 861 462 8302 10
          • 412 455 5625 10
          • 996 451 8884 10
          • 1608 446 8351 10
          • 1376 443 1735 10
          • 1330 430 5182 10
          • 1633 425 2206 10
          • 1197 419 3468 10
          • 441 416 2508 10
          • 424 413 997 10
          • 1298 397 3810 10
          • 1125 395 2299 10
          • 333 393 926 10
          • 363 388 4477 10
          • ....
    • pk5561 + 0 comments

      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.

    • helenxu + 1 comment

      Have you figured out why? I have the same question.

      • thesilviupop + 0 comments

        puzzles me too.

    • fengli97 + 0 comments
      [deleted]
    • sowmithreddy + 0 comments

      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.

    • azam58 + 0 comments

      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'.
    • gatsby_yun + 0 comments

      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;