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 7in 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? |
|
|
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 16and 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. |
|
|
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 16and 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. |
|
|
madrak
Starting Member
20 Posts |
Posted - 2008-10-27 : 14:56:29
|
quote: Originally posted by visakh16But 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 |
|
|
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 SORTAS(SELECT ROW_NUMBER() OVER (order by sortorder) as SortRoder, PKFROM Table)UPDATE TableSET SortOrder = SORT.SortOrderWHERE Table.PK = SORT.Pk |
|
|
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 SORTAS(SELECT ROW_NUMBER() OVER (order by sortorder) as SortRoder, PKFROM Table)UPDATE TableSET SortOrder = SORT.SortOrderWHERE 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. |
|
|
kolka
Starting Member
1 Post |
|
|