You are viewing a single comment's thread. Return to all comments →
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?