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;
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)
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.
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
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.
edit: gold? for me? thanks kind stranger!
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?
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
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!
thank u
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
simple solution:
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 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
This is called attention to details.
lol
nice joke lol
Irony! She is supposed to be the smartest. But then she was told to learn SQL :P
lol