Using SQL UPDATE with ROW_NUMBER()

The problem:

The application is displaying a list of “To-do” tasks from a SQL Server database table, but the rows are currently displayed in an arbitrary order which is not necessarily in the order the user wants them to be displayed. We want to add a new feature to the application to allow the user to specify the order of the To-do items in the list (i.e. a priority for each To-do item).

We want to introduce a new “sort order” (or sequence number) column to the table to allow the user to specify the order in which the rows are to be displayed.

Adding a new column to the “create table” script (for new databases) is obviously straightforward.  But the problem is that we also need to be able to upgrade (migrate) existing customers to the new table structure.

Therefore we need to create a SQL migration script to add a new priority / sort-order column to the existing Todo table.

Currently the Todo table structure looks like this:

create table Todo
(
	TodoId bigint not null primary key identity,
	Priority nvarchar(10) not null,
	Title nvarchar(200) not null,
	DateDone datetime null
)

The following query used by the application to retrieve the Todo list illustrates the problem:

select * from Todo order by Priority

Result:

Notice above that the first two ‘High’ priority items are shown in an arbitrary order.  We may want to order it by the “TodoId” column which is perhaps slightly better, but still not necessarily in the order the user wants to see the tasks.

The solution:

We have to produce two SQL scripts: one is the script used for new installations, the second is to upgrade/migrate existing databases to the new structure.

The first part is easy: for new installation script we just add the new column:

sortOrder int not null

But in the migration script for existing databases, we initially have to add the new “sortOrder” column as a nullable field since there are already rows in the table and we need to provide reasonable initial/default value for the new column first.  So we first add the column as a nullable, and we’ll alter the table afterwards to make it ‘not null’.

So the first step in the migration script is to add the new field to the existing Todo table:

alter table Todo add sortOrder int null

Now we need to update the Todo table to provide an appropriate initial value for the new sortOrder field in each row.

Our Todo tasks are priortised as ‘High’, ‘Medium’ or ‘Low’.  We want the sortOrder value to start from 1 and increment for each subsequent row within each of these 3 priorties.  In other words, we want the two ‘High’ priority tasks to be given a sortOrder value of 1 and 2, the same for the two ‘Medium’ tasks, and the three ‘Low’ priority tasks need to be given a sortOrder of 1, 2, and 3.

The trick is to use the SQL UPDATE statement together the SQL Server ROW_NUMBER() function.

First, let’s just select the data using the ROW_NUMBER() function to see how we can get an appropriate initial value for sortOrder for each row:

select TodoId, Priority, Title,
	ROW_NUMBER() over(partition by Priority order by TodoId) as rowIndex
	from Todo
	order by Priority, rowIndex

Result:

The “rowIndex” value in the result above gives us the correct value we’d like to update the new sortOrder column with for each row.

Now we need to combine this query with a SQL UPDATE statement to update sortOrder:

update t
	set sortOrder = rowIndex
from
(
	select sortOrder, ROW_NUMBER() over(partition by Priority order by TodoId) as rowIndex
	from Todo
	where sortOrder is null
) as t

Now “sortOrder” has the desired values partitioned by priority.

The last step is just to make the sortOrder column not null:

alter table Todo alter column sortOrder int not null

Also see:

• How to use row_number() in SQL Server (StackOverflow)