Re: How to Resequence a Numeric SQL Column?

Hi Dave,

You may be able to use something modeled after this example:

create table qtemp.TEST ( ID, GROUP_NAME, GROUP_SEQUENCE ) as (
values ( int( 1 ), char( 'A', 10 ), smallint( 10 ) )
,( 2 , 'A' , 20 )
,( 3 , 'A' , 30 )
,( 4 , 'A' , 40 )
,( 5 , 'A' , 50 )
) with data
;
update qtemp.TEST U set GROUP_SEQUENCE = 5 where ID = 3
;
update qtemp.TEST U
set GROUP_SEQUENCE =
( select 10 * ( count(*) + 1 ) as NEW_GROUP_SEQUENCE
from qtemp.TEST S
where S.GROUP_NAME = U.GROUP_NAME
and S.ID <> U.ID
and ( S.GROUP_SEQUENCE < U.GROUP_SEQUENCE
or ( S.GROUP_SEQUENCE = U.GROUP_SEQUENCE
and S.ID < U.ID
)
)
)
;
select * from qtemp.TEST
;

Mike

date: Tue, 29 Mar 2016 12:04:56 -0400

from: dlclark@xxxxxxxxxxxxxxxx
subject: How to Resequence a Numeric SQL Column?

Basically, what I am trying to accomplish is to resequence, in
place, a numeric column from 10 by 10. This is used in a priority list
where an entry's priority can change. Thus, for example, if there are 5
entries in a particular group and the entry at priority 30 is changed to
priority 5, I then want to resequence that group of entries from 10 such
that the changed entry will remain with the highest priority of 10 --
i.e., raising it from 30 to 10 -- and moving all other entries in the
group down by 10 for each existing entry. I've been able to resequence
the column but I have not been able to resequence according to the new
priority sequence -- rather, it resequences by the primary identity key no
matter what I try (as shown in the example below). How can I accomplish
this?

Alter Sequence TEMP_SEQ Restart with 10;

Update Turnover_Waiting_List
Set Wait_Sequence = NEXT VALUE FOR TEMP_SEQ
Where TOWAITLT_ID IN (Select TOWAITLT_ID
From Turnover_Waiting_List
Where Item_Name = 'TOWAITBRSM'
And Item_Type = '*MODULE'
And Item_Attribute = 'SQLRPM'
Order by Item_Name, Item_Type, Item_Attribute,
Wait_Sequence)
With NC;

Sincerely,

Dave Clark
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300

Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331