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

|

1899 Discussions

|

  • gollasriharsha80
     + 0 comments

    SELECT w.id, wp.age, w.coins_needed, w.power FROM Wands w JOIN Wands_Property wp ON w.code = wp.code WHERE wp.is_evil = 0 AND w.coins_needed = ( SELECT MIN(w2.coins_needed) FROM Wands w2 JOIN Wands_Property wp2 ON w2.code = wp2.code WHERE wp2.is_evil = 0 AND wp2.age = wp.age AND w2.power = w.power ) ORDER BY w.power DESC, wp.age DESC;

  • hemanth_92
     + 0 comments

    /* ms sql */

    select id,age,coins,power from (select w.id,wp.age,w.coins_needed,w.power, row_number() over(partition by wp.age,w.power order by w.coins_needed) from wands w join wands_property wp on w.code = wp.code and wp.is_evil = 0) nw(id,age,coins,power,rn) where rn = 1 order by power desc,age desc

  • yash_mohan81101
     + 1 comment

    Not able to use CTE in MySQL

  • debjyoti_ghosh
     + 0 comments
    /* MS - SQL Server */
with cte as
(
select
    w.id,
    wp.age,
    w.coins_needed,
    w.power,
    dense_rank() over(partition by w.power, wp.age order by w.coins_needed) dr
from
    Wands w
left join Wands_Property wp on w.code = wp.code
where
    wp.is_evil = 0
)
select id, age, coins_needed, power
from cte
where dr = 1
order by
    power desc,
    age desc;

  • victor_vitchaya
     + 0 comments

    So confused when i'm not a fan of Harry Potter but interesting question!