Zidar - Thursday, March 23, 2017 12:31 PM

This gives the exact same results. Why doesn't DENSE_RANK work?

because of my stupidity. I had PARTITION BY, when there was no need for one. This returms 1 for all rows:

; WITH [a] AS
(
SELECT 9990 [col1] UNION ALL
SELECT 9990 UNION ALL
SELECT 9995 UNION ALL
SELECT 9995 UNION ALL
SELECT 9999
)
SELECT [col1],
row = DENSE_RANK() OVER (PARTITION BY [col1] ORDER BY [col1])
FROM [a]


Thank you Drew for helping.🙂