Update sort Column linked with group

Question

How can i change the position of one row to change the order Best to explain with example

I have following table with statuses

Id Name     StatusOrder StatusGroup
1  Open     1           1
2  Start    2           1
3  Load     3           1
4  Close    4           1
5  Begin    1           2
6  Open     2           2
7  Close    3           2

I would like to Switch from group one only Status order 2 with 3. The jump can be more than one row, ex. its also possible that within the same group the order from open moves to status order 3

Sow when i do following select

SELECT * FROM Status WHERE (StatusGroup =1)

Result Set:

Id Name     StatusOrder StatusGroup
1  Open     1           1
3  Load     2           1
2  Start    3           1
4  Close    4           1
5  Begin    1           2
6  Open     2           2
7  Close    3           2

I already found example with following article but i do not succeed in it to intgrate that only for one group the order changes Using a sort order column in a database table

How Can help me?

So you want to change only for first group (where lowest Id) and only 2 columns `Id` and `Name`? End of each group is Name value `Close`? — Stanislovas Kalašnikovas, Jan 11 '16 at 13:09

Stanislovas Kalašnikovas · Accepted Answer · 2016-01-12T08:04:24.687

If correctly understood, here you go:

QUERY

create table #t
(
    Id INT,
    Name VARCHAR(20),
    StatusOrder INT,
    StatusGroup INT
)
insert into #t values 
(1  ,'Open',    1 , 1),
(2  ,'Start',   2 , 1),
(3  ,'Load',    3 , 1),
(4  ,'Close',   4 , 1),
(5  ,'Begin',   1 , 2),
(6  ,'Open',    2 , 2),
(7  ,'Close',   3 , 2)

;with cte as (
select *, row_number() over(partition by StatusGroup order by Id) rn 
from #t
)
select case when StatusOrder = 2 then 3 when StatusOrder = 3 then 2 else Id end as Id,
       case when StatusOrder = 2 then 'Load' when StatusOrder = 3 then 'Start' else Name end as Name,
       StatusOrder,
       StatusGroup
from cte 
where rn = id

union all

select Id, Name, StatusOrder, StatusGroup
from cte
where rn <> id

drop table #t

OUTPUT

Id  Name    StatusOrder StatusGroup
1   Open    1           1
3   Load    2           1
2   Start   3           1
4   Close   4           1
5   Begin   1           2
6   Open    2           2
7   Close   3           2

UPDATE

So if you have table where you need update records you can do something like:

;with cte as (
select *, row_number() over(partition by StatusGroup order by Id) rn 
from #t
)
update t
set t.Id = (case when cte.StatusOrder = 2 then 3 
               when cte.StatusOrder = 3 then 2 else t.Id end),
    t.Name = (case when cte.StatusOrder = 2 then 'Load' 
                   when cte.StatusOrder = 3 then 'Start'  else t.Name end)
from cte
join #t t on cte.id = t.id
where cte.rn = cte.id

How do i convert this to a update statement? to make the changes different? — Jan Van Looveren, Jan 12 '16 at 05:56

Update sort Column linked with group

1 Answers1