0

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?

  • 1
    why do you need to use inner join with table C? it seems you need to use "<" if you don't use descending order. – Nikita Mar 22 at 14:48
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.