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.

Solution 1

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

Solution 2

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.

Solution 3

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

Comments

[Joel]

That would seem to make sense, except I'm not getting any duplicate rows in my results.

[Joel]

Well this query works fine: SELECT Title, HitCount FROM ( SELECT DISTINCT Title, HitCount FROM ou_v_Articles T ) AS Temp ORDER BY HitCount DESC Except I need the ROW_NUMBER for paging.

[Joel]

Good call. I should have seen that. Thanks.