Sam Sam - 3 days ago 5
SQL Question

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]?

Answer

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;
Comments