[SOLVED] RANK & DENSE_RANK as aggregate(not analytic) function

RANK & DENSE_RANK as aggregate(not analytic) function

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.

Solution

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;