Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
veronika.np
Starting Member
29 Posts |
 Posted - 2011-10-10 : 14:20:07
|
| hi friends,i have a question.i have a table that it has id,name,rownumber.rownumber is int and it has 1,2,3,4,.. numbers.when i want to insert in table i get max(rownumber) and insert max(rownumber)+1 for new row.but i want to when delete a row ,for example when delete a row that rownumber is 3 i want to refresh rownumber and shift rownumber.how can ido it?please help me.thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
veronika.np
Starting Member
29 Posts |
Posted - 2011-10-10 : 14:48:57
|
| no,my rownumbere is not identity,and i should not have a gap for this field(rownumber) .i set value for this field ,for first row set rownumber=1 and for another row get max(rownumber) and set max(rownumber)+1.i want to know how can shift them? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-11 : 01:24:21
|
quote:
Originally posted by veronika.np
no,my rownumbere is not identity,and i should not have a gap for this field(rownumber) .i set value for this field ,for first row set rownumber=1 and for another row get max(rownumber) and set max(rownumber)+1.i want to know how can shift them?
if its not identity then wats the problem? you can always check max value and add 1 to it rightif you're deleting out of sequence also the logic in posted link will help you to shift records to empty positions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-11 : 01:28:48
|
Since you are using SQL 2005, you can make use of row_number() window function to create a continuous row number in your select query
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
shift id of row after delete row