CODEWITHSUNDEEP
sqlsql-server
Sam
Sam

Reputation: 29009

Fill new SQL column with rising integers with order by

I've added an integer row SortNo to an existing SQL table.

Now I want to fill the new integer column SortNo with integers rising by 10 (10, 20, 30, ...), BUT I want the rows to be ordered by another nvarchar column Name.

I can't just use Update, since it would set the SortNo in a random order, not ordered by Name.

So, how can I fill SortNo ordered by Name?

Upvotes: 1

Views: 155

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Try This..

        UPDATE x
        SET x.[SortNo] = x.[SortNo]
        FROM (
              SELECT *, ROW_NUMBER() OVER (ORDER BY [Name]) * 10 AS [SortNo]
              FROM table     
              ) x

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270503

You can do this using an updatable CTE and window functions:

with toupdate as (
      select t.*,
             row_number() over (order by [Name]) as seqnum
      from t
     )
update toupdate
    set sortno = 10 * seqnum;

Upvotes: 4

Related Questions