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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Updating a display order field.

Author  Topic 

madrak
Starting Member

20 Posts

Posted - 2008-10-26 : 19:51:50
I have a table listing items that need to be displayed in a specific order. I'm using a DisplayOrder field in the database to accomplish this. What I have been doing when I remove an item in the middle of the list is updating all of the items with display orders above the removed item and decrementing their display order.

While the above works, I was wondering if there was a way to do one query against the table and update the display orders on all records? Basically it would take DisplayOrders like

1 2 3 5 6 8 10

and turn them into

1 2 3 4 5 6 7

in a single query.

I'm considering in the stored procedure to select the items into a temp table using an autoincrement id field then selecting those rows back into the main table forcing the DisplayOrders to be correct. I could also use a cursor to do it, but they're supposed to be bad, so I was wondering if there was a way to do it in a single query?

Thanks for any help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 00:09:49
Not sure why you want renumber all. Even if you remove one of items in between, wont the other numbers still be in same order, only diffrenece being we will have a gap in b/w but that wont affect the order in which values are retrieved.
if we have 1234567 and ifi remove 5th record the remaining will be 123467 which will still get retrived same order as before. then can you specify the purpose behind renumbering?
Go to Top of Page

madrak
Starting Member

20 Posts

Posted - 2008-10-27 : 11:22:53
quote:
Originally posted by visakh16

then can you specify the purpose behind renumbering?



The reason I would like to renumber is so that when I go to make changes later the numbers are in a predictable order. For example, if my Display Orders are

1 3 8 10 12 15 16

and I go to insert a record at position #3, I would somehow need to find out that position #3 has a DisplayOrder value of 8, not the 3 I would be expecting.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 11:28:16
quote:
Originally posted by madrak

quote:
Originally posted by visakh16

then can you specify the purpose behind renumbering?



The reason I would like to renumber is so that when I go to make changes later the numbers are in a predictable order. For example, if my Display Orders are

1 3 8 10 12 15 16

and I go to insert a record at position #3, I would somehow need to find out that position #3 has a DisplayOrder value of 8, not the 3 I would be expecting.


But why do you want to insert records in between. thats really like complicating the matter. Assuming your number field is identity you dont have to do anything particular to keep the order. you just need to ensure you insert records one after the other so that they will get consecutive numbers. Even if you remove any one in between rest will still be in order and can easily track recently inserted ones by retrieveing in decreasing order of number.
Go to Top of Page

madrak
Starting Member

20 Posts

Posted - 2008-10-27 : 14:56:29
quote:
Originally posted by visakh16

But why do you want to insert records in between. thats really like complicating the matter. Assuming your number field is identity you dont have to do anything particular to keep the order. you just need to ensure you insert records one after the other so that they will get consecutive numbers. Even if you remove any one in between rest will still be in order and can easily track recently inserted ones by retrieveing in decreasing order of number.



That's just it, the number field is not the identity. It is a separate field. I probably wasn't clear enough to begin with, what I have here is a grouping of elements on a page that need to be reordered occasionally, so I will have to be able to insert new records effectively between existing records.

A friend of mine gave me the answer:

update [table] set sortorder = (select count(*) from [table] as t2 where t2.sortorder<=[table].sortorder)

That will do what I need in one query.

Thanks for your help visakh16
Go to Top of Page

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2008-10-27 : 15:40:31
This will probably be a more efficient method that they one you listed.

;WITH SORT
AS

(
SELECT ROW_NUMBER() OVER (order by sortorder) as SortRoder
, PK
FROM Table
)

UPDATE Table
SET SortOrder = SORT.SortOrder
WHERE Table.PK = SORT.Pk
Go to Top of Page

madrak
Starting Member

20 Posts

Posted - 2008-10-27 : 15:49:27
quote:
Originally posted by cr8nk

This will probably be a more efficient method that they one you listed.

;WITH SORT
AS

(
SELECT ROW_NUMBER() OVER (order by sortorder) as SortRoder
, PK
FROM Table
)

UPDATE Table
SET SortOrder = SORT.SortOrder
WHERE Table.PK = SORT.Pk



Thank you for the suggestion, but unfortunately I am still on SQL Server 2000 for this project and don't have the ROW_NUMBER() function available to me. Guess I left a bunch of stuff out of my description of the problem.
Go to Top of Page

kolka
Starting Member

1 Post

Posted - 2015-03-03 : 11:34:32
Here I explain how to resort:

https://kolka.wordpress.com/2015/03/03/how-to-update-sort-field-with-ms-sql-to-create-gaps/

Go to Top of Page
   

- Advertisement -