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
|
1776 Discussions
|
Please Login in order to post a comment
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
Why the following code runs in MS SQL SERVER but not in MySQL?
WITH min_coins AS ( SELECT ROW_NUMBER () OVER (PARTITION BY code, power ORDER BY code, power) as rownumber, id, code, coins_needed, power FROM Wands ) SELECT * FROM min_coins;
SELECT w.id, wp.age, filt.cost, w.power FROM WANDS w JOIN WANDS_PROPERTY wp on w.code = wp.code JOIN ( SELECT filt_wp.age, MIN(filt_w.coins_needed) AS cost, filt_w.power FROM WANDS filt_w JOIN WANDS_PROPERTY filt_wp on filt_w.code = filt_wp.code WHERE filt_wp.is_evil = 0 GROUP BY filt_wp.age, filt_w.power ) AS filt ON filt.age = wp.age AND filt.power = w.power AND filt.cost = w.coins_needed ORDER BY w.power DESC, wp.age DESC;
MySQL