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 |
|
Wilco
Starting Member
9 Posts |
 Posted - 2007-09-14 : 16:33:45
|
Say I have a table with a field called "ordernum" that denotes the order of a given set of rows. Now imagine that I delete one of these rows. What type of query would work best for re-assigning the order numbers so that they remain sequential?Here's an example:id group_id name ordernum--------------------------------------------------0 0 'Name1' 11 0 'Name2' 22 0 'Name3' 33 1 'Name4' 14 1 'Name5' 2 Now if I deleted the column with id='1' how would I reset the values in the 'ordernum' field for that specific group? Is this even possible? |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-14 : 16:39:52
|
An update statement, if you want it something that just figures it out you would need to build it with whatever logic is behind the grouping.
Future guru in the making. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-15 : 02:57:53
|
UPDATE fSET f.OrderNum = f.NewOrderNumFROM (SELECT ordernum, row_number() over (partition by group_id order by id) AS newordernum from table) As f
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-16 : 03:48:31
|
| Peso: Perhaps add a WHERE to only update the rows HIGHER than the "gap"? Thus fewer rows involved in the Update (on average!)Kristen |
|
|
|
|
|
|
Re-Assigning Order Numbering