如何使用ROW_NUMBER和order by

SELECT *
FROM My table A
ORDER BY ROW_NUMBER() OVER(ORDER BY 1)

While using this getting error as Windowed functions and NEXT VALUE FOR functions do not support integer indices as ORDER BY clause expressions.

使用此获取错误作为Windowed函数和NEXT VALUE FOR函数时，不支持整数索引作为ORDER BY子句表达式。

How i can make it work.

我怎么能让它发挥作用。

TIA

4 个解决方案

#1

Your query makes no sense. You are not really specifying an order by condition in the outer query, so why bother?

你的查询毫无意义。您实际上并没有在外部查询中按条件指定订单，所以为什么要这么麻烦？

Perhaps you want to add a "row number" value to the output. If so, then the row_number() belongs in the select, not the order by:

也许您想在输出中添加“行号”值。如果是这样，那么row_number（）属于select，而不是order by：

SELECT A.*, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM Mytable A;

SQL Server does not permit constant values in ORDER BY -- either in a window function or in the ORDER BY clause. The SELECT NULL subquery is a way around this. Normally, an integer expression in an ORDER BY is an index, referring to the column to be ordered. That works for the ORDER BY clause, but not for a window function. SQL Server also rejects other constants.

SQL Server不允许ORDER BY中的常量值 - 在窗口函数或ORDER BY子句中。 SELECT NULL子查询是解决这个问题的一种方法。通常，ORDER BY中的整数表达式是一个索引，指的是要排序的列。这适用于ORDER BY子句，但不适用于窗口函数。 SQL Server还拒绝其他常量。

In my experience, this does not result in an additional sort. To the best of my knowledge, this is not documented, however.

根据我的经验，这不会产生额外的排序。据我所知，这并没有记录在案。

#2

you can't use row_number() function in order by clause .you can do something like this..

你不能在order by子句中使用row_number（）函数。你可以做这样的事情..

SELECT ROW_NUMBER() OVER(ORDER BY 1) as Rno, * FROM My table A order by rno

要么

 with cte as
    (
    SELECT ROW_NUMBER() OVER(ORDER BY 1) as Rno, * FROM My table A 
    )
    select * from cte ORDER BY Rno

#3

Try this:

尝试这个：

SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as RN
FROM My_table_A
ORDER BY RN;

#4

This can be rewritten as:

这可以改写为：

SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN
FROM [My table] AS A
ORDER BY RN;

#1