Implementation of DENSE_RANK

Question

I am trying to implement the DENSE_RANK function of SQL Server using only basic SQL functions. I managed to implement the RANK function but I don't know how extend it to DENSE_RANK. More specifically, I don't know how to arbitrarily distinsguish between rows of the same rank.

Here is my implementation of RANK.

SELECT A.partition_id,
    A.order_column_id,
    COUNT(B.order_column_id) AS rank
FROM MyTable AS A
INNER JOIN MyTable AS B
    ON A.partition_id = B.partition_b AND A.order_column_id <= B.order_column_id
GROUP BY A.partition_id, A.order_column_id

How can I write an implementation of DENSE_RANK in a similar way?

Answer 1

Just use COUNT(DISTINCT instead of COUNT( - because that’s essentially the difference between DENSE_RANK and RANK.