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...
  • Practice
  • Compete
  • Jobs
  • Leaderboard
  1. Practice
  2. SQL
  3. Basic Join
  4. Ollivander's Inventory
  5. Discussions

Ollivander's Inventory

  • Problem
  • Submissions
  • Leaderboard
  • Discussions

Sort 331 Discussions, By:

votes
  • recency
  • votes

Please Login in order to post a comment

  • chenjesu 2 years ago+ 11 comments

    Hermione's reasoning is flawed. The wand chooses the wizard, not the other way around.

    470|
    Permalink
    • davi418 2 years ago+ 0 comments

      LOL

      0|
      ParentPermalink
    • Hari_MBIH 2 years ago+ 0 comments
      [deleted]
      0|
      ParentPermalink
    • torab_shaikh 2 years ago+ 0 comments

      hahaha ;)

      0|
      ParentPermalink
    • purplegal1976 2 years ago+ 0 comments

      You win the internet.

      12|
      ParentPermalink
    • xy2286 2 years ago+ 0 comments

      the ultimate truth!

      0|
      ParentPermalink
    • dannywin19901102 1 year ago+ 0 comments

      LOL It's definately a flaw.

      0|
      ParentPermalink
    • te9dinne 1 year ago+ 0 comments

      Lol, that's so true!

      0|
      ParentPermalink
    • hariom7793 1 year ago+ 1 comment

      rofl...

      0|
      ParentPermalink
      • jagatsemwal 1 year ago+ 1 comment

        Complicated yet simple :) Below is my code in MySQL

        SELECT temp2.I, temp2.A, temp2.WNN, temp2.P FROM (SELECT MIN(W1.COINS_NEEDED) AS WN, WP1.AGE as AG, W1.POWER AS PW FROM WANDS W1 INNER JOIN WANDS_PROPERTY WP1 ON W1.CODE=WP1.CODE 
         GROUP BY W1.POWER, WP1.AGE ORDER BY W1.POWER DESC, WP1.AGE DESC) temp1
        INNER JOIN
        (SELECT W.ID AS I, MIN(W.COINS_NEEDED) AS WNN, WP.AGE as A, W.POWER AS P  FROM WANDS W INNER JOIN WANDS_PROPERTY WP ON W.CODE=WP.CODE 
        WHERE WP.IS_EVIL=0
        GROUP BY W.POWER, WP.AGE, W.ID ORDER BY W.POWER DESC, WP.AGE DESC) temp2
        ON temp1.WN=temp2.WNN AND temp1.PW=temp2.P AND temp1.AG=temp2.A;
        
        -14|
        ParentPermalink
        • akshaypanchal23 1 year ago+ 2 comments

          Here is my simple solution

          select a.id, b.age, a.coins_needed, a.power from Wands a inner join Wands_Property b on a.code=b.code where b.is_evil!=1 and a.coins_needed=(select min(Wands.coins_needed) from Wands inner join Wands_Property on Wands.code=Wands_Property.code where Wands_Property.age=b.age and Wands.power=a.power) order by a.power desc,b.age desc

          21|
          ParentPermalink
          • zdravko2 11 months ago+ 0 comments

            Could you explain why is it that if I have

            Wands_Property.id = a.id
            

            in the final where clause I get duplicates (power and age) in the result.

            0|
            ParentPermalink
          • anarchtheist 3 months ago+ 1 comment

            This is a little less nested than what I came up with, good job! I tried to figure out a clever way to OVER(PARTITION BY but came up short.

            1|
            ParentPermalink
            • pranu_kvs 1 week ago+ 0 comments

              I did it using partition over.

              SELECT A.myid,A.age,A.coins_needed,A.power from 
                  (SELECT w1.id as myid,age,coins_needed,power,
                  row_number() OVER(PARTITION BY age,power 
                  ORDER BY coins_needed asc) as rn
                      FROM wands w1 inner join 
                  wands_property w2 on w1.code = w2.code
              where is_evil = 0) A
              where A.rn = 1
              order by power desc, age desc;
              
              0|
              ParentPermalink
    • jayakrishnanadi1 8 months ago+ 0 comments

      haha..crct..

      0|
      ParentPermalink
    • sheatsej 4 months ago+ 0 comments

      so true!

      0|
      ParentPermalink
    • REX_37 1 month ago+ 0 comments

      LOL

      0|
      ParentPermalink
  • djgorman 2 years ago+ 3 comments

    Yet another ill-described problem. The minimum cost for each wand is the cost of the wand, right? join to filter evil ones and sort and we're done. Yet, my answer is "incorrect". As I encounter more and more poorly worded problems, the less interested in this site I become. :(

    69|
    Permalink
    • multidynamic 2 years ago+ 1 comment

      There can be multiple wands of the same power and age but of different cost.

      4|
      ParentPermalink
      • GetFuckingGood 1 year ago+ 1 comment

        Yes, but the problem never mentioned to look for the minimum among the different combinations of power and age. It is ill-posed.

        28|
        ParentPermalink
        • benazus 11 months ago+ 0 comments

          Finally I know why my code doesn't work... Thank you.

          7|
          ParentPermalink
    • scott123 2 years ago+ 0 comments

      Welcome to industry ;)

      14|
      ParentPermalink
    • do_well_better_1 4 months ago+ 0 comments

      I agree. If the quality continues to be this uneven I'll abandon the site.

      5|
      ParentPermalink
  • dun_zhang2012 3 years ago+ 14 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

    32|
    Permalink
    • menaeffat 3 years ago+ 0 comments

      Good job

      0|
      ParentPermalink
    • davi418 2 years ago+ 1 comment

      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

      31|
      ParentPermalink
      • emmafreeman 2 years ago+ 0 comments

        I agree, the instructions are not explicit in asking us to do a subquery to find the minimum coins needed.

        16|
        ParentPermalink
    • chadwalt 2 years ago+ 0 comments

      nicely done

      0|
      ParentPermalink
    • tingyuk 2 years ago+ 2 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
      

      to

      group by w1,power, p1.age
      

      Appreciate it!

      1|
      ParentPermalink
      • shanu2608 2 years ago+ 2 comments

        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.

        8|
        ParentPermalink
        • tingyuk 2 years ago+ 0 comments

          Thank you for your explanation. This is really clear and helpful!

          2|
          ParentPermalink
        • bonniegu0429 2 years ago+ 1 comment

          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)
          
          0|
          ParentPermalink
          • brubru777 2 years ago+ 0 comments

            "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.

            0|
            ParentPermalink
      • emmafreeman 2 years ago+ 5 comments

        I am confused as to why we need this line:

        where w1.power = w.power and p1.age = p.age
        

        Could someone please explain it's function?

        5|
        ParentPermalink
        • roopabnr 2 years ago+ 0 comments

          ^WSS can anyone explain the need of the above statement?

          0|
          ParentPermalink
        • tsaihan_lin90 2 years ago+ 0 comments

          I don't understand this part too, why we have to match two tables that are exactly the same?

          2|
          ParentPermalink
        • aaayala3 2 years ago+ 1 comment

          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.

          1|
          ParentPermalink
          • mehul_sachdeva7 2 months ago+ 0 comments

            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

            0|
            ParentPermalink
        • rahul_rowthi 1 year ago+ 0 comments

          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.

          1|
          ParentPermalink
        • willinlondon 1 year ago+ 0 comments

          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.

          6|
          ParentPermalink
    • kajalverma 2 years ago+ 0 comments
      [deleted]
      0|
      ParentPermalink
    • jbdfr_dnph 2 years ago+ 0 comments

      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;

      -4|
      ParentPermalink
    • decimal_simplex 2 years ago+ 5 comments

      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:

      select wands.id, min_prices.age, wands.coins_needed, wands.power
      from wands
      inner join (select wands.code, wands.power, min(wands_property.age) as age, min(wands.coins_needed) as min_price
                  from wands
                  inner join wands_property
                  on wands.code = wands_property.code
                  where wands_property.is_evil = 0
                  group by wands.code, wands.power) min_prices
      on wands.code = min_prices.code
         and wands.power = min_prices.power
         and wands.coins_needed = min_prices.min_price
      order by wands.power desc, min_prices.age desc
      
      7|
      ParentPermalink
      • LinVenus 2 years ago+ 0 comments

        Agreed! That is what I did as well.

        0|
        ParentPermalink
      • RaviPushkar 2 years ago+ 1 comment

        I think

        wands.power = min_prices.power
        

        is redundant since you are already grouping it with wands.code The query should work without it.

        1|
        ParentPermalink
        • LearnCrazy 2 years ago+ 1 comment

          @ravi: Could you please explain the above query. Thank you.

          0|
          ParentPermalink
          • LearnCrazy 2 years ago+ 1 comment

            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!

            0|
            ParentPermalink
            • frozendamn 2 years ago+ 1 comment

              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 !!

              0|
              ParentPermalink
              • wm3wy 2 years ago+ 0 comments

                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.

                0|
                ParentPermalink
      • zhangzz2015 2 years ago+ 0 comments

        would you please tell me why min(wands_property.age) as age? I think for a certain code, the age is fixed. thank you!

        0|
        ParentPermalink
      • anandrajt 1 year ago+ 0 comments

        why do we need a min(age) though? i seem to have been able to get it without this step.

        0|
        ParentPermalink
      • ArielJiang 11 months ago+ 1 comment

        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.

        0|
        ParentPermalink
        • victoreufrasio 10 months ago+ 0 comments

          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.

          0|
          ParentPermalink
    • yisilala 2 years ago+ 0 comments

      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)?

      3|
      ParentPermalink
    • rodbourn 2 years ago+ 2 comments

      I had to read this to understand the question properly.

      Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age.

      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.

      13|
      ParentPermalink
      • JiaminWANG 11 months ago+ 0 comments

        I dont undrstand at all what it means..... could you please explain more

        0|
        ParentPermalink
      • diyasher 4 weeks ago+ 0 comments

        Serioulsy, poorly defined problem

        0|
        ParentPermalink
    • fengli97 2 years ago+ 0 comments

      i guess it

       'groupy by' takes several rows and turns them into one row. Because of this, it has to know what to do with all the combined rows where there have different values for some columns (fields). This is why you have two options for every field you want to SELECT : Either include it in the GROUP BY clause, or use it in an aggregate function so the system knows how you want to combine the field.
      

      from http://stackoverflow.com/questions/20074562/group-by-without-aggregate-function

      1|
      ParentPermalink
    • pleple 2 years ago+ 0 comments

      The condition

      p1.age = p.age
      

      is not needed for the mapping between code and age is one-one

      2|
      ParentPermalink
    • Retributor 2 years ago+ 0 comments

      hey bruh can u clearly explain your answer?

      0|
      ParentPermalink
    • Anzelm 11 months ago+ 0 comments

      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).

      0|
      ParentPermalink
    • gnemlock 5 months ago+ 0 comments

      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.

      0|
      ParentPermalink
  • stevepobox_hack1 2 years ago+ 0 comments

    Badly-written question. Nowhere is it made clear that it's looking for the cheapest wand for a given combination of age and power. I had to come to this discussion section to find that out.

    25|
    Permalink
  • kshuckerow 2 years ago+ 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;
    
    21|
    Permalink
    • kshuckerow 2 years ago+ 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

      1|
      ParentPermalink
      • deepak119 1 year ago+ 1 comment

        have you figured out why?

        0|
        ParentPermalink
        • deepak119 1 year ago+ 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
          • ....
          -11|
          ParentPermalink
    • pk5561 2 years ago+ 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.

      -2|
      ParentPermalink
    • helenxu 2 years ago+ 1 comment

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

      0|
      ParentPermalink
      • thesilviupop 2 years ago+ 0 comments

        puzzles me too.

        0|
        ParentPermalink
    • fengli97 2 years ago+ 0 comments
      [deleted]
      0|
      ParentPermalink
    • sowmithreddy 2 years ago+ 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.

      -2|
      ParentPermalink
    • azam58 1 year ago+ 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'.
      
      1|
      ParentPermalink
    • gatsby_yun 4 weeks ago+ 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;

      0|
      ParentPermalink
  • san28v 2 years ago+ 1 comment

    My solution in SQL Server:

    SELECT id, age, coins_needed, power
    FROM 
    (
        SELECT W.id, WP.age, W.coins_needed, W.power,
        ROW_NUMBER() OVER 
            (
                PARTITION BY W.code,W.power  
                ORDER BY W.coins_needed, W.power DESC
            ) AS RowNumber
        FROM Wands W WITH (NOLOCK)
        INNER JOIN Wands_Property WP WITH (NOLOCK) ON W.code = WP.code
        WHERE WP.is_evil = 0
    )
    AS Wand_Data
    WHERE RowNumber = 1
    ORDER BY power DESC, age DESC
    
    19|
    Permalink
    • novi22 2 years ago+ 0 comments

      Your solution is working and the code is concise and simple

      -2|
      ParentPermalink
  • amit_madhup 2 years ago+ 1 comment

    my solution in oracle using window function

    select id,age,coins_needed,power   from (
    select id,age,coins_needed,power, min(coins_needed) over (partition by w.code,age,power) as min_coins from wands w 
    inner join wands_property wp on w.code = wp.code
    where wp.is_evil =0
    ) where coins_needed = min_coins 
    order by power desc,age desc;
    
    5|
    Permalink
    • ianlee313 2 years ago+ 0 comments
      [deleted]
      0|
      ParentPermalink
  • sindhwani_himan1 2 years ago+ 4 comments

    without using join

    select w.id,wp.age,w.coins_needed,w.power from wands as w,wands_property as wp where w.code=wp.code and wp.is_evil=0 and w.coins_needed =(select min(wo.coins_needed) from wands as wo where wo.power=w.power and wo.code=w.code) order by power desc,age desc

    5|
    Permalink
    • dhruman_daxini 2 years ago+ 0 comments

      Why can't we simply write order by coins_needed asc instead of sub query??

      2|
      ParentPermalink
    • zexianxu 2 years ago+ 0 comments

      It seems your solution is still joining the two tables. A solution without joining tables is as follows:

      SELECT id, 
          (SELECT age FROM Wands_Property WHERE code = w.code) AS age,
          coins_needed, 
          power
      
      FROM Wands w
      WHERE w.code IN 
          (SELECT code FROM Wands_Property WHERE is_evil = 0)
      AND coins_needed = 
          (SELECT MIN(coins_needed) FROM Wands 
           WHERE code = w.code AND power = w.power)
      
      ORDER BY power DESC, age DESC;
      
      8|
      ParentPermalink
    • Venkateswaran 1 year ago+ 0 comments

      w.coins_needed =(select min(wo.coins_needed) from wands as wo where wo.power=w.power and wo.code=w.code)

      what is the use of w0.power=w.power and w0.code=w.code..? I am highly confused. Help me with this...

      0|
      ParentPermalink
    • anandrajt 1 year ago+ 1 comment

      i am nt sure this works. also, you are using the functionality of join. jst not using the keyword.

      0|
      ParentPermalink
      • vidya_vidyam 1 year ago+ 0 comments

        select d.id,table2.age,table2.cn,table2.pw from (select table1.age age,min(table1.cn) cn,table1.pw pw from (select b.age age,a.coins_needed cn, a.power pw from wands a , wands_property b where a.code=b.code and b.is_evil=0 )table1 group by table1.age,table1.pw)table2, wands_property c, wands d where table2.age=c.age and c.code=d.code and table2.cn=d.coins_needed order by 4 desc, 2 desc ;

        this is in oracle sql

        2|
        ParentPermalink
  • raufaliyev40 2 years ago+ 3 comments

    Select w.id, wp.age,w.coins_needed,w.power From Wands w, Wands_Property wp where w.code=wp.code and w.coins_needed IN ( select Min(coins_needed) from Wands wan where wan.code= wp.code and wp.is_evil=0 group by wan.power ) order by w.power desc,wp.age desc;

    That's my solution in oracle.here you are.

    4|
    Permalink
    • mrengineer1451 2 years ago+ 1 comment

      iste bu congratulations bro ;)

      1|
      ParentPermalink
      • qayibxanova1995 2 years ago+ 1 comment

        mrengineer1451 discussiona az bax. ozun yaz selectlerini

        2|
        ParentPermalink
        • mrengineer1451 2 years ago+ 0 comments

          okay :)

          2|
          ParentPermalink
    • qayibxanova1995 2 years ago+ 0 comments

      Amazing mate diyicem ama demiyom)

      1|
      ParentPermalink
    • raufaliyev40 2 years ago+ 0 comments

      cox sag ol bro :))

      0|
      ParentPermalink
  • wjldrew 2 years ago+ 3 comments

    MySQL. In the beginning I had trouble with the output because SELECT output wands with a minimum coins needed that didn't always match with that wand.

    There is a sub-query to make sure that if the wand age and wand power matches with another, to return the cheapest (lowest coins_needed) wand with.

    SELECT W.id, P.age, W.coins_needed, W.power
    FROM WANDS AS W
        INNER 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
            INNER 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
    
    3|
    Permalink
    • achalshah20 2 years ago+ 1 comment

      Can you please explain how

      W.coins_needed = 
      (SELECT MIN(coins_needed)
      FROM WANDS AS W1
      INNER JOIN WANDS_PROPERTY AS P1 ON W1.code = P1.code
      WHERE W1.power = W.power AND P1.age = P.age)
      

      is different than

      group by W.power, W.age
      having MIN(W.coins_needed)
      

      Thanks

      1|
      ParentPermalink
      • payalbhatia_ece 11 months ago+ 0 comments

        Please follow the link and interpret the output by running query separately :

        https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns

        -1|
        ParentPermalink
    • yingli40604345 2 years ago+ 0 comments

      May I know what does this part mean? Why use W1 with W and P1 with P?Thank you so much!!! SELECT MIN(coins_needed) FROM WANDS AS W1 INNER JOIN WANDS_PROPERTY AS P1 ON W1.code = P1.code WHERE W1.power = W.power AND P1.age = P.age)

      -1|
      ParentPermalink
    • christianwarnold 7 months ago+ 0 comments

      select s.id, s.age, s.coins_needed, s.power from (select w.id, wp.age, w.coins_needed, w.power from Wands as w inner join Wands_Property as wp on w.code = wp.code) as s inner join (select wp.age, min(w.coins_needed) as coins_needed, w.power from Wands as w inner join Wands_Property as wp on w.code = wp.code where wp.is_evil = 0
      group by wp.age, w.power order by w.power desc, wp.age desc) as t on s.age = t.age and s.coins_needed = t.coins_needed and s.power = t.power order by t.power desc, t.age desc

      0|
      ParentPermalink
Load more conversations

Need Help?


View top submissions
  • Contest Calendar
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy
  • Request a Feature