- Prepare
- SQL
- Basic Join
- Ollivander's Inventory
- Discussions
Ollivander's Inventory
Ollivander's Inventory
+ 0 comments select w.id, wp.age, w.coins_needed, w.power from wands as w inner join (select code, min(coins_needed) as cn, power from wands group by code, power) mc on w.code=mc.code and w.coins_needed=mc.cn and w.power=mc.power inner join wands_property as wp on w.code=wp.code where wp.is_evil=0 order by w.power desc, wp.age desc;
+ 0 comments MySql 5.7
SELECT w.id, wp.age, w.coins_needed, w.power FROM wands w JOIN wands_property wp ON wp.code = w.code WHERE wp.is_evil = 0 AND w.coins_needed = ( SELECT MIN(coins_needed) FROM wands derived_w JOIN wands_property derived_wp ON derived_wp.code = derived_w.code WHERE derived_wp.age = wp.age AND derived_w.power = w.power ) ORDER BY w.power DESC, wp.age DESC ;
+ 0 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;
+ 0 comments With Cte as (select WP.age as Cte_Age, min(W.coins_needed) as Cte_Coins,W.power as Cte_Power 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 max(A.id) ,Cte_Age,Cte_Coins,Cte_Power from Cte inner join Wands A on A.coins_needed = Cte_Coins and A.power = Cte_Power inner join Wands_Property B on B.Age = Cte_Age where B.is_evil = 0 group by Cte_age,Cte_Coins, Cte_Power order by Cte_Power desc, Cte_Age desc
+ 1 comment With Cte as (select WP.age as Cte_Age, min(W.coins_needed) as Cte_Coins,W.power as Cte_Power 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 max(A.id) ,Cte_Age,Cte_Coins,Cte_Power from Cte inner join Wands A on A.coins_needed = Cte_Coins and A.power = Cte_Power inner join Wands_Property B on B.Age = Cte_Age where B.is_evil = 0 group by Cte_age,Cte_Coins, Cte_Power order by Cte_Power desc, Cte_Age desc
Sort 1491 Discussions, By:
Please Login in order to post a comment