chenjesu Hermione's reasoning is flawed. The wand chooses the wizard, not the other way around.
davi418 LOL
Hari_MBIH [deleted]
torab_shaikh hahaha ;)
purplegal1976 You win the internet.
JXG776 edit: gold? for me? thanks kind stranger!
xy2286 the ultimate truth!
dannywin19901102 LOL It's definately a flaw.
te9dinne Lol, that's so true!
hariom7793 rofl...
jagatsemwal 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;
akshaypanchal23 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
zdravko2 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.
anarchtheist 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.
pranu_kvs 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;
AkshayKReddy WHat does A.rn = 1 imply?
npsmith79 A.rn = 1 means that the wand with specific age, power combo has the minimum number of coins_needed (because the rows were sorted in ascending order by coins_needed)
lily_zhang622 have you considered using MIN() OVER (PARTION ... instead?
tripurajupudi can you explain me the query you written
AkshayKReddy Hey Tripura,
If you are looking for an explanation of @akshaypanchal23's query (as per the nested level of your comment), start from inside.
Min Coins calculation: This is where you find out min. coins needed provided that the power and age are same. As you need non-evil wands, you use both conditions non-evil wands and minimum coins needed using an AND.
If you don't use this condition, you will end up getting all the rows with same age, power combination.
Table replication is needed in order for the min. coins condition to work.
Usage of JOINS:
JOINS were used in sub-query and the outer query as we need age from a different table.
Finally, you sort the outer query using power first and age next.
P.S:
You can also self-explain the query by neatly arranging the outer query, KEYWORDS, inner query.
Cheers!
tripurajupudi thank u
nigam_sparsh You don't have to use join in the subquery... instead of using 'age' use 'code'.
woldegebrielkeb1 here is my simple and easy to understand solution
select t2.i, t.a, t.c, t.p from (select wp.age a ,min(w.coins_needed) c , max(w.power) p from Wands w, Wands_Property wp where wp.code=w.code and wp.is_evil=0 group by wp.age, w.power) as t, (select ww.id i , wpp.age aa , ww.coins_needed cc , ww.power pp from Wands ww, Wands_Property wpp where wpp.code=ww.code and wpp.is_evil=0) as t2 where t2.aa=t.a and t2.cc=t.c and t2.pp=t.p order by t.p desc, t.a desc;
siyanlin 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, 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 ) temp2 ON temp1.WN=temp2.WNN AND temp1.PW=temp2.P AND temp1.AG=temp2.A ORDER BY temp2.P DESC, temp2.A DESC, temp2.I;
jayakrishnanadi1 haha..crct..
sheatsej so true!
REX_37 LOL
anugrahgupta_52 This is called attention to details.
nithyasaim lol
joekishore999 nice joke lol
harish1729 Irony! She is supposed to be the smartest. But then she was told to learn SQL :P
djgorman 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. :(
multidynamic There can be multiple wands of the same power and age but of different cost.
GetFuckingGood Yes, but the problem never mentioned to look for the minimum among the different combinations of power and age. It is ill-posed.
benazus Finally I know why my code doesn't work... Thank you.
bhavukkhanna Can you (or someone else) help me?
I understand the question i.e for each combination of age and power you need to find the wand with minimum cost that is not evil.
I came up with this:
SELECT MIN(w.coins_needed),w.power,wp.age from wands w inner join wands_property wp on w.code= wp.code where wp.is_evil=0 group by w.power,wp.age order by w.power desc, wp.age desc;
The solution requires me to add ID of the wand and I am struggling with the same. Any Idea how to get the ID infront of my solution
clarksonchang hi bhavukkhanna,
You can get the ID in front by adding one more condition in the WHERE statement after your JOIN. This is to to get the row with the minimum coins needed that matches the AGE and POWER. Please find the query below:
SELECT ID, AGE, COINS_NEEDED, POWER FROM (WANDS w JOIN WANDS_PROPERTY p ON w.Code = p.Code) WHERE p.is_evil = 0 AND w.coins_needed = ( SELECT MIN(coins_needed) FROM Wands w1 JOIN WANDS_PROPERTY p1 ON (w1.code = p1.code) WHERE w1.power = w.power AND p1.age = p.age ) ORDER BY POWER DESC, AGE DESC
scott123 Welcome to industry ;)
do_well_better_1 I agree. If the quality continues to be this uneven I'll abandon the site.
dun_zhang2012 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
menaeffat Good job
davi418 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
emmafreeman I agree, the instructions are not explicit in asking us to do a subquery to find the minimum coins needed.
maybe_mae Thank you both for pointing out that I need to learn how to do subqueries to get this to work.
I don't know how to do those and I'd never think that I'd have to learn something that hadn't been mentioned in any problem previously to do "basic joins".
chadwalt nicely done
tingyuk 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!
shanu2608 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.
tingyuk Thank you for your explanation. This is really clear and helpful!
bonniegu0429 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)
brubru777 "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.
emmafreeman 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?
roopabnr ^WSS can anyone explain the need of the above statement?
tsaihan_lin90 I don't understand this part too, why we have to match two tables that are exactly the same?
aaayala3 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.
mehul_sachdeva7 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
rahul_rowthi 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.
willinlondon 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.
wongj19 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.
pcnapanmolina By doing:
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
kajalverma [deleted]jbdfr_dnph 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;
decimal_simplex 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
LinVenus Agreed! That is what I did as well.
RaviPushkar I think
wands.power = min_prices.power
is redundant since you are already grouping it with wands.code The query should work without it.
LearnCrazy @ravi: Could you please explain the above query. Thank you.
LearnCrazy 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!
frozendamn 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 !!
wm3wy 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.
zhangzz2015 would you please tell me why min(wands_property.age) as age? I think for a certain code, the age is fixed. thank you!
anandrajt why do we need a min(age) though? i seem to have been able to get it without this step.
ArielJiang 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.
victoreufrasio 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.
fabioyang781 What's the difference between this inner query and the outer query tho? are they returns the same thing?
yisilala 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)?
rodbourn 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.
JiaminWANG I dont undrstand at all what it means..... could you please explain more
diyasher Serioulsy, poorly defined problem
fengli97 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
pleple The condition
p1.age = p.age
is not needed for the mapping between code and age is one-one
Retributor hey bruh can u clearly explain your answer?
Anzelm 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).
gnemlock 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.
ddhar49 Why doesn't the min coin selection subquery need to check whether or not evil is 0? What if the min(coins_needed) is from a wand which is evil?
stevepobox_hack1 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.
kshuckerow 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 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 have you figured out why?
deepak119 - 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 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 Have you figured out why? I have the same question.
thesilviupop puzzles me too.
fengli97 [deleted]sowmithreddy 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 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 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;
yerzhik 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.
sourabhsingh What do you mean by HAVING MIN(coins_needed)?
san28v 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
novi22 Your solution is working and the code is concise and simple
vedsetu An alternate yet similar methodology:
select id, age, coins_needed, power from (
select id, age, coins_needed, power,
min(coins_needed) over(partition by age, power order by coins_needed) as min_coins from wands w inner join wands_property w_p on w.code=w_p.code where is_evil=0 )a where coins_needed - min_coins=0 order by power desc, age desc
amit_madhup 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;
ianlee313 [deleted]
raufaliyev40 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.
mrengineer1451 iste bu congratulations bro ;)
qayibxanova1995 mrengineer1451 discussiona az bax. ozun yaz selectlerini
mrengineer1451 okay :)
qayibxanova1995 Amazing mate diyicem ama demiyom)
raufaliyev40 cox sag ol bro :))
sindhwani_himan1 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
dhruman_daxini Why can't we simply write order by coins_needed asc instead of sub query??
zexianxu 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;
fadil_ritonga what is the use of code = w.code and power = w.power ? why do we have to equate these same two columns?
Venkateswaran 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...
anandrajt i am nt sure this works. also, you are using the functionality of join. jst not using the keyword.
vidya_vidyam 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
wjldrew MySQL. In the beginning I had trouble with the output because
SELECToutput 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
achalshah20 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
payalbhatia_ece Please follow the link and interpret the output by running query separately :
https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns
yingli40604345 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)
christianwarnold 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
Sort 464 Discussions, By:
Please Login in order to post a comment