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.
  1. Prepare
  2. SQL
  3. Basic Join
  4. Ollivander's Inventory
  5. Discussions

Ollivander's Inventory

Sort by

recency

|

1877 Discussions

|

  • gauravpathak293
     + 0 comments

    with min_coin as ( select code, power, min(coins_needed) as coins_required from Wands group by code, power )

    Select w.id, wp.age, w.coins_needed, w.power from Wands w join Wands_property wp on w.code = wp.code join min_coin mc on w.code = mc.code and w.power = mc.power where wp.is_evil = 0 and w.coins_needed = mc.coins_required order by w.power desc, wp.age desc

  • ros_alex79
     + 0 comments
    with tmp as (
    select power, age, w.code, min(coins_needed) as price
    from wands as w
    inner join wands_property as wp on w.code = wp.code
    where is_evil = 0
    group by power, age, w.code
)
select id, age, price, tmp.power
from tmp
inner join wands as w on tmp.power = w.power and tmp.code = w.code
and price = coins_needed
order by tmp.power desc, age desc;

  • ngoccth
     + 0 comments

    ngoccth_SQL SERVER: WITH table_1 AS ( SELECT id, age, MIN(coins_needed) AS Min, [power] , row_number () OVER (PARTITION BY [power], age ORDER BY MIN(coins_needed)) AS num FROM Wands LEFT JOIN Wands_Property ON Wands.code = Wands_Property.code WHERE is_evil = 0 GROUP BY id, age, [power] ) SELECT id, age, Min, [power] FROM table_1 WHERE num = 1 ORDER BY power DESC, age DESC

  • sy77736
     + 0 comments

    WITH rankedWands AS ( SELECT W.id, W.code, WP.age, W.coins_needed, W.power, RANK() OVER (PARTITION BY W.code, WP.age ,w.power ORDER BY W.coins_needed ASC) AS rank FROM Wands AS W INNER JOIN Wands_Property AS WP ON W.code = WP.code WHERE WP.is_evil = 0 ) SELECT id, age, coins_needed, power FROM rankedWands WHERE rank = 1 -- Selects the rows with the minimum coins_needed per code and age ORDER BY power DESC, age DESC;

  • raghaw21
     + 0 comments

    -- query to print id,age,coins_need and power of the wands sorted by order of descending power -- if power is same sort the results by descending age --lets get a CTE for minimum coins needed WITH coins_min AS ( SELECT code, power, MIN(coins_needed) AS minimum_coins FROM wands GROUP BY code, power ) SELECT w.id, p.age, w.coins_needed, w.power FROM wands w JOIN wands_property p ON w.code = p.code JOIN coins_min c ON w.code = c.code AND w.power = c.power WHERE p.is_evil = 0 AND w.coins_needed = c.minimum_coins ORDER BY w.power DESC, p.age DESC;