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

|

2070 Discussions

|

  • juancamilo28acos
     + 0 comments

    /* w -> alias de la tabla Wands wp -> alias de la tabla Wands_Properties w2 -> wp2 -> alias of Wands_Property in subquery c -> Resultado de la Subconsulta */

    -- Instrucciones

    SELECT w.id, wp.age, w.coins_needed, w.power FROM Wands w

    -- JOIN que busca traer propiedades principales a la consulta principal

    JOIN Wands_Property wp ON w.code=wp.code -- Usar codigos validos en wp

    -- JOIN busca conectar cada varita con su propiedad para poder agrupar por edad y descartar las varitas malignas antes de calcular el minimo.

    JOIN ( SELECT w2.power, wp2.age, MIN(w2.coins_needed) c FROM Wands w2 JOIN Wands_Property wp2 ON w2.code=wp2.code WHERE wp2.is_evil=0 GROUP BY w2.power, wp2.age -- Calcular el minimo, agrupando por el mismo poder y edad )

    m ON m.power=w.power AND m.age=wp.age AND m.c=w.coins_needed -- Unir la subconsulta con las tablas principales

    WHERE wp.is_evil=0 ORDER BY w.power DESC, wp.age DESC; -- Ordenar primero el poder de manera desendente, si hay empate entonces la edad

  • allanmaakh7
     + 0 comments

    select id, age, coins_needed, power from( select w.id, wp.age, w.coins_needed, w.power , ROW_NUMBER() over(partition by power,age order by w.coins_needed) as ranking from wands w join wands_property wp on w.code = wp.code where wp.is_evil = 0) where ranking = 1 order by power desc, age desc;

  • Thilak05
     + 0 comments
    SELECT w.id, p.age, w.coins_needed, w.power
FROM wands w
JOIN wands_property p ON w.code = p.code
WHERE p.is_evil = 0
AND w.coins_needed = (
    SELECT MIN(w2.coins_needed)
    FROM wands w2
    JOIN wands_property p2 ON w2.code = p2.code
    WHERE p2.age = p.age AND w2.power = w.power AND p2.is_evil = 0
)
ORDER BY w.power DESC, p.age DESC;

  • eddy_rene_c
     + 1 comment
    WITH cte1 as (
    select id,
            age,
            coins_needed,
             power,
            row_number() over( partition by age,power order by coins_needed ) as rnk
    from wands a 
    left join 
    wands_property b
    on a.code = b.code
    where is_evil = 0    
)

select id, age, coins_needed, power
from 
cte1
where rnk =1 
order by power desc, age desc;

  • cjcwork1
     + 0 comments

    SELECT id, age, coins_needed, power FROM ( SELECT id, age, coins_needed, power, ROW_NUMBER() OVER(PARTITION BY age, power ORDER BY coins_needed) rk FROM Wands w JOIN Wands_Property wp ON w.code=wp.code WHERE is_evil=0 ) WHERE rk=1 ORDER BY power DESC, age DESC ;