How to use ROW_NUMBER with order by

Question

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.

How i can make it work.

TIA

Answer 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:

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.

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

Answer 2

This can be rewritten as:

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