Custom SQL is Jet SQL in the legacy connection and Jet SQL did not have Rank functions. When you bring these values into tableau you should then be able to use Tableau's Rank function. I believe RANK() has existed since tableau 8.1.
I figured this was the case
The rank was more of a pre-requisite to creating a running total based on the rank so that I can do some what-if analysis to it
Essentially saying, using the above example, if I invested £x (based on column Cost), how much Cost Benefit would I get in return (Column Benefit Cost)
£x being a parameter
So, in the above example, if I invested £10 I would get £1 Benefit Cost but if I invested £30 I would get £11 Benefit cost