T-SQL SELECT DISTINCT & ROW_NUMBER () TASK TASKS

Question

I am trying to select DISTINCT strings from a view using ROW_NUMBER () OVER for swap. When I switched the ORDER BY field from SMALLDATETIME to INT, I began to get strange 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, but when I uninstall DISTINCT, it arranges them correctly.

Thanks.

Answer 1

Is the RowId value correct? Perhaps you just need an ORDER BY RowId clause in an external query?

Answer 2

Applying DISTINCT to a list of columns containing ROW_NUMBER () will always result in each row being different, since there is one row for each row in ROW_NUMBER.

Answer 3

Have you tried just using an external selection order and deleting the OVER clause?