Reordering SQL Position Column

Question

I have a TO DO Table with a Position Column. (other columns removed for simplicity)

Description varchar(max)
Position int
JobFinished bit

If I have n amount of rows, each time a row is added the position is incremented. So values may look like this:

Drop Off Kids,   0, 0 
Get Groceries,   1, 0 
Pickup Kids,     2, 0

Now, If I do something I want to remove it from the table. And increment all other position columns so they are in order from 0.

Ie If I Get Groceries I need the values to look like this:

Drop off Kids,  0,    0
Get Grocerieis, NULL, 1
Pickup Kids,    1,    0

Is there any way with SQL I could do this? (Im using SQL server)

Similar to: http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table/8608085#8608085 — xQbert, Dec 28 '11 at 01:45

score 1 · Accepted Answer · answered Dec 28 '11 at 01:45

1

This is very simple SQL question, just check your sql server reference, for the update SQL statement:

update todo_table set position = position -1 where position > 1;
update todo_table set position = NULL, JobFinished = 1 where Description='Get Grocerieis'

answered Dec 28 '11 at 01:45

Stevens Wu

26
2

Hi, But this will not always work. If there is 10 items, and I remove the item at position 8. Then run this query. This will effect all the items under 8. However I have realised i Can do update tablre set postion = position - 1 where position > (position to be removed).. It is a lot simplier now I think about it. thanks – Michael Dec 28 '11 at 01:52

score 1 · Answer 2 · answered Dec 28 '11 at 01:48

1

Assuming negative incremental is the only concern and not re-arranging and that you have a way to "Group" your sets.. then

update toDO set position = position -1 where position > @recordDeletedPosition and GroupingMethod = something

answered Dec 28 '11 at 01:48

xQbert

34,733
2
41
62

Igor Borisenko · Answer 3 · 2011-12-28T02:05:10.477

1

Although it looks like a bad design you can try this solution with ROW_NUMBER function (SQL Server 2005 +)

DECLARE @tbl table(i int)

INSERT INTO @tbl VALUES (0),(null),(2),(null),(null),(5)

UPDATE n
SET i=rn-1
FROM (SELECT i,ROW_NUMBER() OVER(ORDER BY i) rn
      FROM @tbl
      WHERE not i is null) n




SELECT *  FROM @tbl

edited Dec 28 '11 at 02:05

answered Dec 28 '11 at 01:55

Igor Borisenko

3,806
3
34
49

score 0 · Answer 4 · answered Dec 28 '11 at 10:59

0

with cte as (
   select Position, row_number() over (order by position) - 1 as NewPosition
   from tbl
   where JobFinished = 0
)
update tbl
set Position = NewPosition

answered Dec 28 '11 at 10:59

Ben Thul

31,080
4
45
68

Reordering SQL Position Column

4 Answers4