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.
WITH preferred_wand_choices_cte
AS (
SELECT wands.id,
property.age,
wands.coins_needed,
wands.power,
RANK() OVER (
PARTITION BY wands.power,
property.age ORDER BY wands.power DESC,
coins_needed ASC
) AS ranking
FROM wands
INNER JOIN wands_property AS property
ON wands.code = property.code
AND property.is_evil = 0
)
SELECT id,
age,
coins_needed,
power
FROM preferred_wand_choices_cte
WHERE ranking = 1
ORDER BY power DESC,
age DESC
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Ollivander's Inventory
You are viewing a single comment's thread. Return to all comments →
MS SQL SERVER
WITH preferred_wand_choices_cte AS ( SELECT wands.id, property.age, wands.coins_needed, wands.power, RANK() OVER ( PARTITION BY wands.power, property.age ORDER BY wands.power DESC, coins_needed ASC ) AS ranking FROM wands INNER JOIN wands_property AS property ON wands.code = property.code AND property.is_evil = 0 ) SELECT id, age, coins_needed, power FROM preferred_wand_choices_cte WHERE ranking = 1 ORDER BY power DESC, age DESC