[Hacker Rank_MySQL]문제 풀이#Ollivander's Inventory

SQL > Basic Join > Ollivander's Inventory

Ollivander's Inventory | HackerRank

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

The mapping between code and age is one-one, meaning that if there are two pairs, (code1, age1) and (code2, age2), then code1 ≠ code2, age1≠age2.

빨간 글씨는 내가 문제를 풀 때 중요하다고 생각한 부분이다.

 

 

SELECT W.ID, WP.AGE, X.CN, W.POWER
FROM (SELECT CODE, MIN(COINS_NEEDED) CN, POWER
     FROM WANDS
     GROUP BY CODE, POWER) X 
JOIN
WANDS W
ON (X.CODE = W.CODE)
    AND (X.POWER = W.POWER)
    AND (X.CN = W.COINS_NEEDED)
JOIN
WANDS_PROPERTY WP
ON W.CODE = WP.CODE
WHERE WP.IS_EVIL = 0
ORDER BY W.POWER DESC, WP.AGE DESC