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

|

2092 Discussions

|

  • saurabhvitkare20
     + 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 w2.power = w.power AND wp2.age = wp.age ) ORDER BY w.power DESC, wp.age DESC;

  • S_tnieks
     + 0 comments

    SQL SERVER

    SELECT 
wands.id, 
wands_property.age, 
wands.coins_needed, 
wands.power
FROM wands
JOIN wands_property
ON wands.code = wands_property.code
WHERE wands_property.is_evil = 0
AND wands.coins_needed =
(
SELECT min(wands_2.coins_needed)
FROM wands wands_2
JOIN wands_property wp2
ON wands_2.code = wp2.code
WHERE wands_2.power = wands.power
AND wp2.age = wands_property.age

    ) ORDER BY wands.power DESC, wands_property.age DESC;

  • noahwood94
     + 0 comments

    Holy subquery

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

  • jdamianmijangos
     + 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 w2.power = w.power AND wp2.age = wp.age )
ORDER BY w.power DESC, wp.age DESC;

/*
WITH MinCoins AS (
  SELECT 
    w.id, 
    wp.age, 
    w.power, 
    w.coins_needed, 
    MIN(w.coins_needed) OVER (PARTITION BY wp.age, w.power) AS min_coins
  FROM Wands w
  JOIN Wands_Property wp ON w.code = wp.code
  WHERE wp.is_evil = 0
)
SELECT id, age, coins_needed, power FROM MinCoins
WHERE coins_needed = min_coins
ORDER BY power DESC, age DESC;
*/

  • jachi334
     + 0 comments
    select W2.id, WP2.age, W2.coins_needed, W2.power
from wands as W2
join wands_property as WP2 on W2.code = WP2.code
join
    (select 
        WP.age as age,
        W.power as powerr,
        min(W.coins_needed) as best_price 
    from wands as W
    join wands_property as WP on W.code = WP.code
    where WP.is_evil = False
    group by WP.age, W.power)
    as TMP1
on WP2.age = TMP1.age and W2.power = TMP1.powerr and W2.coins_needed = TMP1.best_price
order by W2.power desc, WP2.age desc