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

I'm trying to select DISTINCT rows from a view using ROW_NUMBER() OVER for paging. When I switched the OR开发者_Go百科DER 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.

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.

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

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