You are viewing a single comment's thread. Return to all comments →
Here is my simple solution
select a.id, b.age, a.coins_needed, a.power from Wands a inner join Wands_Property b on a.code=b.code where b.is_evil!=1 and a.coins_needed=(select min(Wands.coins_needed) from Wands inner join Wands_Property on Wands.code=Wands_Property.code where Wands_Property.age=b.age and Wands.power=a.power) order by a.power desc,b.age desc
Could you explain why is it that if I have
Wands_Property.id = a.id
in the final where clause I get duplicates (power and age) in the result.
This is a little less nested than what I came up with, good job! I tried to figure out a clever way to OVER(PARTITION BY but came up short.
I did it using partition over.
SELECT A.myid,A.age,A.coins_needed,A.power from
(SELECT w1.id as myid,age,coins_needed,power,
row_number() OVER(PARTITION BY age,power
ORDER BY coins_needed asc) as rn
FROM wands w1 inner join
wands_property w2 on w1.code = w2.code
where is_evil = 0) A
where A.rn = 1
order by power desc, age desc;
WHat does A.rn = 1 imply?
A.rn = 1 means that the wand with specific age, power combo has the minimum number of coins_needed (because the rows were sorted in ascending order by coins_needed)
have you considered using MIN() OVER (PARTION ... instead?
can you explain me the query you written
If you are looking for an explanation of @akshaypanchal23's query (as per the nested level of your comment), start from inside.
Min Coins calculation:
This is where you find out min. coins needed provided that the power and age are same. As you need non-evil wands, you use both conditions non-evil wands and minimum coins needed using an AND.
If you don't use this condition, you will end up getting all the rows with same age, power combination.
Table replication is needed in order for the min. coins condition to work.
Usage of JOINS:
JOINS were used in sub-query and the outer query as we need age from a different table.
Finally, you sort the outer query using power first and age next.
You can also self-explain the query by neatly arranging the outer query, KEYWORDS, inner query.
You don't have to use join in the subquery... instead of using 'age' use 'code'.
here is my simple and easy to understand solution
select t2.i, t.a, t.c, t.p
from (select wp.age a ,min(w.coins_needed) c , max(w.power) p
from Wands w, Wands_Property wp
where wp.code=w.code and wp.is_evil=0
group by wp.age, w.power) as t,
(select ww.id i , wpp.age aa , ww.coins_needed cc , ww.power pp
from Wands ww, Wands_Property wpp
where wpp.code=ww.code and wpp.is_evil=0) as t2
where t2.aa=t.a and t2.cc=t.c and t2.pp=t.p
order by t.p desc, t.a desc;