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.
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
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;
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;
Ollivander's Inventory
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.
LOL
hahaha ;)
You win the internet.
the ultimate truth!
LOL It's definately a flaw.
Lol, that's so true!
rofl...
Complicated yet simple :) Below is my code in MySQL
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
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.
WHat does A.rn = 1 imply?
can you explain me the query you written
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;
haha..crct..
so true!
LOL