T-SQL SELECT DISTINCT& ROW_NUMBER()个订购问题 [英] T-SQL SELECT DISTINCT & ROW_NUMBER() OVER Ordering Problem

问题描述

我试图从使用ROW_NUMBER()OVER进行分页的视图中选择DISTINCT行.当我将ORDER BY字段从SMALLDATETIME切换为INT时，我开始得到怪异的结果:

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

此查询返回:

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

结果显然不正确.我不确定这是什么问题，但是当我删除DISTINCT时，它会正确订购它们.

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.

谢谢.

推荐答案

RowId值是否正确?也许您只需要在外部查询上使用ORDER BY RowId子句?

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

这篇关于T-SQL SELECT DISTINCT& ROW_NUMBER()个订购问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！