sql - I am trying to provide a ranking based on a previous partition

Question

I am trying to change the ranking column where instead of ordering by RMSE ASC I want to order by RMSESum ASC (which was a previous partition by)….when I do this I get an error that "ordered analytic functions cannot be nested" any idea around this?

Select material, plant, fiscper, dmd_final_Fcst, sum_final_dmd_qty, 
row_number() Over (partition by material, plant order by **RMSE ASC**) as **Ranking**,
Sum(RMSE) over (partition by material, plant) as **RMSESum**,
Power(Fcst_er,2) As Sq_er,
dmd_final_fcst - sum_final_dmd_qty As Fcst_Er,
Sqrt(MSE) As RMSE,
Avg(Sq_er) as MSE 
from PRD_DWH_PURCHASING.HistoricalForecastVal
group by 1,2,3,4,5;

question from:https://stackoverflow.com/questions/65517176/i-am-trying-to-provide-a-ranking-based-on-a-previous-partition

Answer 1

You should use the sub-query as follows:

SELECT T.*,
       row_number() Over (partition by material, plant order by RMSESum ASC) as Ranking
FROM
(Select material, plant, fiscper, dmd_final_Fcst, sum_final_dmd_qty,
       Sum(RMSE) over (partition by material, plant) as RMSESum,
       Power(Fcst_er,2) As Sq_er,
       dmd_final_fcst - sum_final_dmd_qty As Fcst_Er,
       Sqrt(MSE) As RMSE,
       Avg(Sq_er) as MSE
  from PRD_DWH_PURCHASING.HistoricalForecastVal
 group by 1,2,3,4,5) t;