Can I order by rank or dense rank in ntile function?

Question

I was looking for some help in regards to finding out whether I can use the rank/dense rank function within an ntile query.

I have the following code that I have written:

NTILE(5) OVER (PARTITION BY JOB_TYPE ORDER BY SCORE DESC) AS M_NTILE
,DENSE_RANK() OVER (PARTITION BY MONTH_ENDING, JOB_TYPE ORDER BY M_NTILE DESC) AS RANK

but what I want to do instead of the order by score in the ntile I would like this to be ordered by the results in the rank query.

Is this possible? If somebody could please advise would really appreciate it.

Answer 1

You would need to use a subquery:

select t.*,
       DENSE_RANK() OVER (PARTITION BY MONTH_ENDING, JOB_TYPE
                          ORDER BY M_NTILE DESC) AS RANK
from (select . . .,
             NTILE(5) OVER (PARTITION BY JOB_TYPE ORDER BY SCORE DESC) AS M_NTILE
      . . .
      ) t 

However, that really doesn't seem necessary. Why not just use score?

select t.*,
       DENSE_RANK() OVER (PARTITION BY MONTH_ENDING, JOB_TYPE
                          ORDER BY SCORE DESC) AS RANK