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.
You can get the ID in front by adding one more condition in the WHERE statement after your JOIN. This is to to get the row with the minimum coins needed that matches the AGE and POWER. Please find the query below:
SELECT ID, AGE, COINS_NEEDED, POWER
FROM
(WANDS w
JOIN WANDS_PROPERTY p
ON w.Code = p.Code)
WHERE p.is_evil = 0 AND w.coins_needed =
(
SELECT MIN(coins_needed)
FROM
Wands w1
JOIN WANDS_PROPERTY p1
ON (w1.code = p1.code)
WHERE
w1.power = w.power AND p1.age = p.age
)
ORDER BY POWER DESC, AGE DESC
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Ollivander's Inventory
You are viewing a single comment's thread. Return to all comments →
hi bhavukkhanna,
You can get the ID in front by adding one more condition in the WHERE statement after your JOIN. This is to to get the row with the minimum coins needed that matches the AGE and POWER. Please find the query below:
SELECT ID, AGE, COINS_NEEDED, POWER FROM (WANDS w JOIN WANDS_PROPERTY p ON w.Code = p.Code) WHERE p.is_evil = 0 AND w.coins_needed = ( SELECT MIN(coins_needed) FROM Wands w1 JOIN WANDS_PROPERTY p1 ON (w1.code = p1.code) WHERE w1.power = w.power AND p1.age = p.age ) ORDER BY POWER DESC, AGE DESC