Update order of something

query.sql

declare @ProjectID int, @SectionID int, @NewOrder int
set @ProjectID = 48
set @SectionID = 36
set @NewOrder = 4

;with NewOrder1 as (
    select ID, [Order], rank() over (order by [Order]) as n, 1 as m 
    from ProjectDescriptionSection 
    where [Order] <= @NewOrder and ProjectID = @ProjectID and ID <> @SectionID
    union all
    -- .5 - less than last previous row if last previous row has same order
    select ID, [Order], @NewOrder - .5 as n, 1 as m 
    from ProjectDescriptionSection 
    where ID = @SectionID
    union all
    select ID, [Order], rank() over (order by [Order]) as n, 2 as m 
    from ProjectDescriptionSection 
    where [Order] > @NewOrder and ProjectID = @ProjectID and ID <> @SectionID
), NewOrder2 as (
    select ID, [Order], row_number() over (order by m, n) as r from NewOrder1
)

select * from NewOrder2

--update ProjectDescriptionSection
--set [Order] = n.r
--from ProjectDescriptionSection c
--inner join NewOrder2 n on n.ID = c.ID

sample results

ID       Order   r
-------  ------- -------
37       1       1
35       3       2
34       4       3
36       2       4
33       5       5
424      6       6