CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1844634
user1844634

Reputation: 1263

How to use ROW_NUMBER with 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.

How i can make it work.

TIA

Upvotes: 3

Views: 8287

Answers (4)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

This can be rewritten as:

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 10

Rahul Tripathi
Rahul Tripathi

Reputation: 172618

Try this:

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

Upvotes: 1

Dhaval
Dhaval

Reputation: 2379

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

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

or

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

Upvotes: 0

Related Questions