CODEWITHSUNDEEP
sqlsql-server
Jayant Satkar
Jayant Satkar

Reputation: 53

SQL Server RowNumbering

SrNo   TextCol
--------------
NULL   ABC
NULL   ABC
NULL   ASC
NULL   qwe

I want to update the SrNo column with numbers 1,2,3,4 without changing sequence of other columns.

Upvotes: 0

Views: 46

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Tables are unordered, so you cannot rely on "existing sequence". However a "trick" is to use select null which in effect does nothing to the row order. While it works you should not rely on it as a permanent solution.

WITH cte AS (
        SELECT SrNo, TextCol
            , ROW_NUMBER() OVER (ORDER BY (select NULL)) rn
        FROM yourTable
     )
UPDATE cte
SET SrNo = rn;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

It only makes sense to speak of using row number if there exist a column which can provide ordering. Assuming the ordering is specified by the TextCol column, then we can try the following:

WITH cte AS (
    SELECT SrNo, TextCol, ROW_NUMBER() OVER (ORDER BY TextCol) rn
    FROM yourTable
)

UPDATE cte
SET SrNo = rn;

Upvotes: 1

Related Questions