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?