Numbering the rows by using PARTITION BY to filter results

I am trying to use partition by to name the rows and then eliminate the rows that are not needed in the solution. The error message I receive is: “Invalid column name ‘row_num’ “

The question is as 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.

Wands table consists of id, code, coins needed, and power Wands_property table consists of code,age, and is_evil (is_evil=0 if it’s not evil)

My suggested code is as following:

select row_number() over(partition by w.power,wp.age order by w.coins_needed) row_num,w.id,wp.age,w.coins_needed,w.power from wands w
join wands_property wp
on wp.code=w.code
where wp.is_evil=0 and w.row_num=1
order by w.power desc, wp.age desc;

Appreciate your help in advance!

Answer

WHERE is execute before SELECT, so it doesn’t know what is row_num because it doesn’t exist yet. SQL Order of Execution

select *
from (
    select row_number() over(partition by w.power,wp.age order by w.coins_needed) row_num,w.id,wp.age,w.coins_needed,w.power 
    from wands w
    join wands_property wp on wp.code=w.code
    where wp.is_evil=0
) a
where row_num=1
order by power desc, age desc;

Advertisement

Answer