Group by and minimum aggregator (with join) in sql

Question

I am trying to teach my sql and am doing the hackerrank practice questions. I came across the Olivander's Inventory question, which says the following: Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand. 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. Here is the link for more details. It is unhappy with my solution, and I think it is particularly something to do with the group by. What is wrong with what I have done and how do I fix it? Can I same small modifications to make it correct (i.e. still using the group by clause)?

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.

select w.id, wp.age, min(w.coins_needed), w.power
from wands as w, wands_property as wp
where w.code = wp.code and wp.is_evil = 0
group by w.power, wp.age
order by w.power desc, wp.age desc;

I would appreciate suggestions! Thanks!

score 2 · Accepted Answer · answered Oct 26 '17 at 04:36

It's unhappy with w.id in conjunction with that group by clause. Basically, there can be multiple w.id's corresponding to the same w.power, wp.age pairing. You know that what you want is the ID corresponding to the entry for the min, but that's not (directly) legal SQL. What would you have it do if you had put avg instead of min?

Instead, you'll want to use a subquery. Break the problem up into two parts:

Determine what the minimum number of coins is to acquire a wand of a given power and age (I used code, because they're one-to-one mapped, and it avoids a join)
Determine which ID corresponds to that wand
Do the filtering and ordering on the resulting minimum-priced subset

In other words (using JOIN, because it's generally preferred),

select w.id, wp.age, s.min_coins_needed, s.power
from (select code, power, min(coins_needed) as min_coins_needed
  from wands
  group by power, code) as s 
left join wands w
  on w.coins_needed = s.min_coins_needed and w.code = s.code and w.power = s.power
left join wands_property as wp 
  on w.code = wp.code 
where wp.is_evil = 0
order by s.power desc, wp.age desc;

score 0 · Answer 2 · answered Jul 16 '22 at 06:53

0

SELECT
  w.id,
  wp.age,
  w.coins_needed,
  w.power
FROM
  Wands w 
  LEFT JOIN Wands_property wp ON
  w.code = wp.code
WHERE
  w.coins_needed = 
    (
        SELECT
          MIN(coins_needed) AS min_coins_needed
        FROM
          Wands w1
          LEFT JOIN Wands_property wp1 ON
          w1.code = wp1.code
        WHERE
          wp1.is_evil = 0 AND 
          w1.power = w.power AND
          wp1.age = wp.age
    )
ORDER BY
  w.power DESC,
  wp.age DESC;

answered Jul 16 '22 at 06:53

Ajaikumar A

1
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 18 '22 at 18:04

Group by and minimum aggregator (with join) in sql

2 Answers2