RANK & DENSE_RANK as aggregate(not analytic) function

Question

Is there any option to use RANK/DENSE_RANK function in SQL Server as aggregate? For example the below query in Oracle/Postgres returns hypothetical ranking of the value 200 in the orders table based on the order's gross value.

 SELECT RANK(200) WITHIN GROUP (ORDER BY gross_value ASC)
   FROM orders;

RANK/DENSE_RANK with OVER clause works quite different(as analytic function) and that's not what I want to get.

Answer 1

You can work out what the ranking would be by getting the highest rank for all values below that. If there is none then ISNULL it to 1.

SELECT ISNULL(MAX(rn) + 1, 1)
FROM (
    SELECT rn = RANK() OVER (ORDER BY gross_value)
    FROM orders
    WHERE gross_value < 200
) t;

For descending order you do it like this

SELECT ISNULL(MAX(rn) + 1, 1)
FROM (
    SELECT rn = RANK() OVER (ORDER BY gross_value DESC)
    FROM orders
    WHERE gross_value > 200
) t;