You are viewing a single comment's thread. Return to all comments →
Hermione's reasoning is flawed. The wand chooses the wizard, not the other way around.
You win the internet.
edit: gold? for me? thanks kind stranger!
the ultimate truth!
LOL It's definately a flaw.
Lol, that's so true!
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
(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
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;
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
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.
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.
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;
WHat does A.rn = 1 imply?
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)
have you considered using MIN() OVER (PARTION ... instead?
can you explain me the query you written
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.
You can also self-explain the query by neatly arranging the outer query, KEYWORDS, inner query.
You don't have to use join in the subquery... instead of using 'age' use 'code'.
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;
select id,age,coins_needed,power from
select id,age,coins_needed,power,row_number() over(partition by age,power order by age,coins_needed asc) rnk from wands inner join wands_property on wands.code=wands_property.code where is_evil=0
)src where rnk=1 order by power desc,age desc;
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
(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
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;
This is called attention to details.
nice joke lol
Irony! She is supposed to be the smartest. But then she was told to learn SQL :P