Tutorial :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?


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »