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.
USING MS SQL SERVER BECAUSE MYSQL WOULD NOT ACCEPT ANY FORM OF CTE:
WITH cte1 AS (SELECT
w.id AS cteid,
wp.age AS cteage,
w.coins_needed AS ctecoin,
w.power AS ctepower
FROM Wands w
LEFT JOIN Wands_Property wp ON w.code = wp.code
WHERE wp.is_evil = 0),
cte2 AS (SELECT
wp.age AS cteage,
MIN(w.coins_needed) AS ctecoin,
w.power AS ctepower
FROM Wands w
LEFT JOIN Wands_Property wp ON w.code = wp.code
WHERE wp.is_evil = 0
GROUP BY wp.age, w.power)
SELECT cte1.cteid, cte2.cteage, cte2.ctecoin, cte2.ctepower FROM cte1
RIGHT JOIN cte2 ON cte1.cteage = cte2.cteage AND cte1.ctecoin = cte2.ctecoin
ORDER BY cte2.ctepower DESC, cte2.cteage 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 →
USING MS SQL SERVER BECAUSE MYSQL WOULD NOT ACCEPT ANY FORM OF CTE:
WITH cte1 AS (SELECT w.id AS cteid, wp.age AS cteage, w.coins_needed AS ctecoin, w.power AS ctepower FROM Wands w LEFT JOIN Wands_Property wp ON w.code = wp.code WHERE wp.is_evil = 0),
cte2 AS (SELECT wp.age AS cteage, MIN(w.coins_needed) AS ctecoin, w.power AS ctepower FROM Wands w LEFT JOIN Wands_Property wp ON w.code = wp.code WHERE wp.is_evil = 0 GROUP BY wp.age, w.power)
SELECT cte1.cteid, cte2.cteage, cte2.ctecoin, cte2.ctepower FROM cte1 RIGHT JOIN cte2 ON cte1.cteage = cte2.cteage AND cte1.ctecoin = cte2.ctecoin ORDER BY cte2.ctepower DESC, cte2.cteage DESC;