T-SQL SELECT DISTINCT & ROW_NUMBER() OVER Ordering Problem

Question

I'm trying to select DISTINCT rows from a view using ROW_NUMBER() OVER for paging. When I switched the ORDER BY field from a SMALLDATETIME to INT I started getting weird results:

SELECT RowId, Title, HitCount FROM
( SELECT DISTINCT Title, HitCount, ROW_NUMBER() OVER(ORDER BY HitCount DESC) AS RowId FROM ou_v_Articles T ) AS Temp
WHERE RowId BETWEEN 1 AND 5

This query returns:

RowId | Title | HitCount
=======================
4  ---  9
1  ---  43
3  ---  11
2  ---  13
5  ---  0

The results are obviously not in the correct order. I'm not sure what the problem is here, but when I removed DISTINCT it orders them correctly.

Thanks.

Answer 1

Applying DISTINCT to a column list containing ROW_NUMBER() will always result in every row being distinct, as there is one ROW_NUMBER per row.

Answer 2

Have you tried just using an order by on the outer select and removing the OVER clause?

Answer 3

Is the RowId value you're getting correct? Perhaps you just need an ORDER BY RowId clause on the outer query?