How to generate Serial Number in reverse order with ROW_NUMBER() function in sql server

Question

I am using SQL to generate Serial number in SQL server 2008

SELECT  ROW_NUMBER() OVER ( ORDER BY Column ) AS SrNo

Above SQL will generate SrNo in the ascending order

1 2 3 4 5

but I want to generate it in descending order.

5 4 3 2 1

PowerStar · Accepted Answer · 2017-02-17 07:28:50Z

2

You need to sort the ORDER BY column DESC

SELECT SrNo FROM
(SELECT ROW_NUMBER() OVER (ORDER BY YOUR_COLUMN ) AS SrNo FROM YOUR_TABLE) X
ORDER BY SrNo DESC

answered Feb 17, 2017 at 7:28

PowerStar

8955 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Isn't any idea without using sub query?

Mostly No I guess !! Please anybody correct me if I am wrong (by providing example)

AymDev · Accepted Answer · 2023-06-16 09:50:29Z

1

select * FROM (

  SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO, ColumnName FROM table

 )r

 ORDER BY SNO Desc

AymDev

7,7234 gold badges34 silver badges59 bronze badges

answered Feb 17, 2017 at 7:22

Singh Kailash

6745 silver badges16 bronze badges