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.
- Prepare
- SQL
- Basic Join
- Ollivander's Inventory
- Discussions
Ollivander's Inventory
Ollivander's Inventory
Sort by
recency
|
1681 Discussions
|
Please Login in order to post a comment
The best answer here, unfortunately, is "wrong" as per the simulator. Here's why: The top 3 results for the alleged "correct answer" do not display the best bang for the buck for a given wand of high power and age (look at the second result)
1038 496 4789 10
1130 494 9439 10
1315 492 4126 10
I wrote the following query, which makes sense, achieves the goal of the question like so:
SELECT w.id, p.age, w.coins_needed, w.power FROM Wands w INNER JOIN Wands_Property p ON p.code = w.code WHERE p.is_evil = 0 ORDER by w.power DESC, p.age DESC, w.coins_needed
which results in (for the first 3 rows):
1038 496 4789 10
1303 496 6678 10
1130 494 9439 10
Note the second row. My query correctly orders it - note that, on the second row, the number of galleons for an older level 10 wand is less than the "correct" answer.
This question should be removed from the simulator as the "correct" answer is patently wrong!
Question was retardo patronum but here's mysql query
select t.id, wp.age, t.coins_needed, t.power from (select id, code, power, coins_needed from wands w where id= (select id from wands where power=w.power and code=w.code order by coins_needed asc limit 1)) as t left join wands_property wp on wp.code=t.code where is_evil=0 order by t.power desc, wp.age desc
My code:
SELECT W.id,S.age,S.coins,S.power FROM Wands AS W JOIN (SELECT age,power,MIN(coins_needed) AS coins, Wands.code FROM Wands JOIN Wands_Property ON Wands.code=Wands_Property.code WHERE is_evil=0 GROUP BY code, age,power) AS S ON W.code=S.code and W.coins_needed=S.coins ORDER BY S.power DESC,S.age DESC;
My code:
SELECT W.id,S.age,S.coins,S.power FROM Wands AS W JOIN (SELECT age,power,MIN(coins_needed) AS coins, Wands.code FROM Wands JOIN Wands_Property ON Wands.code=Wands_Property.code WHERE is_evil=0 GROUP BY code, age,power) AS S ON W.code=S.code and W.coins_needed=S.coins ORDER BY S.power DESC,S.age DESC;