CODEWITHSUNDEEP
sqlsql-server-2008
Andyww
Andyww

Reputation: 219

Reordering seq number

I have a table which contains data similar to this

seq Controlid
1   12345
2   12345
5   12345
6   12345
9   12345
10  12345
11  12345
16  12345
1   451251
2   451251
3   451251

I am looking for some way of applying a update command that can reorder the seq column for a particular Controlid – So given the Controlid 12345 after said update I would end up with this data like this:

seq Controlid
1   12345
2   12345
3   12345
4   12345
5   12345
6   12345
7   12345
8   12345
1   451251
2   451251
3   451251

The seq columns datatype is smallint It would always start from 1

(I'm using SQL Server 2008 R2)

Upvotes: 1

Views: 67

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

Try this

select 
       row_number() over (partition by Controlid order by seq) as seq, Controlid
 from your_table

Edit : Since you have no option to use view, try this UPDATE

update t1 
set
t1.seq=t2.seq_new from your_table as t1 inner join
(
select 
           row_number() over (partition by Controlid order by seq) as seq_new, seq,
           Controlid
     from your_table
) as t2 on t1.seq=t2.seq and t1.Controlid=t2.Controlid

Upvotes: 1

PowerStar
PowerStar

Reputation: 895

After reading Madhivanan's comment, This should work for you.

CREATE VIEW YOUR_VIEW_NAME
AS
SELECT O.rn,O.Controlid
from 
(SELECT seq
       ,Controlid
       ,ROW_NUMBER() OVER(PARTITION BY Controlid order by Controlid ) as rn

FROM ORDER_SEQ) O

Upvotes: 0

Related Questions