T-SQL Re-Assigning Order Numbering

Posted by Meltdown on Stack Overflow See other posts from Stack Overflow or by Meltdown
Published on 2010-05-27T23:08:47Z Indexed on 2010/05/27 23:11 UTC
Read the original article Hit count: 350

Filed under:

order

|

assignment

|

t-sql

|

order-numbering

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 active
---------------------------------------------------
0_____0______'Name1'___5__true
1_____0______'Name2'___4__true
2_____0______'Name3'___3__true
3_____1______'Name4'___2__true
4_____1______'Name5'__1__true
5_____1______'Name6'__NULL___false

Now if I deleted the column with id='4' how would I reset the values in the 'ordernum' field for that specific group? Is this even possible?
Or if I added a new row. (The first time the rows are created they are sorted by date, but then the user has the option to set the order himself.)

In my table design I have a column 'active' boolean. If 'active' is set to false, then 'ordernum' is set to NULL. Otherwise it should be given an order number.

© Stack Overflow or respective owner

Related posts about order

CakePHP: find neighbors, order on 'name' or 'order'

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I have a list of ordered items, ordered according to the int field order. I'm creating a gallery in CakePHP 1.2 that has a prev and next button and those should link to the previous and next item according to their ordering, not according to their id. In order to get this result I've included… >>> More
how to get the int order sorting asc, the order field already in varchar type

as seen on Stack Overflow - Search for 'Stack Overflow'
I have the field receiptno as varchar and want to sort this, if normal order sorting getting wrong sorting value. Then have the idea to sort the field by convert int type. Is there anyway to converting int type in query itself >>> More
ASP.NET MVC 2: Mechanics behind an Order / Order Line in a edit form

as seen on Stack Overflow - Search for 'Stack Overflow'
In this question I am looking for links/code to handle an IList<OrderLine> in an MVC 2 edit form. Specifically I am interested in sending a complete order to the client, then posting the edited order back to an object (to persist) using: Html.EditorFor(m = m.orderlines[i]) (where orderlines… >>> More
Treeset to order elements in descending order

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the piece of code that I have used for Java 5.0 TreeSet<Integer> treeSetObj = new TreeSet<Integer>( Collections.reverseOrder() ) ; Collections.reverseOrder() is used to obtain a comparator in order to reverse the way the elements are stored and iterated. Is there a more optimized… >>> More
Search results in wordpress, order by custom category order and time DESC

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello I want to change default post search order to order: sample cat 1 sample post from cat 1 sample post from cat 1 sample cat 2 sample post from cat 2 sample post from cat 3 sample cat 3 etc.. So... I want to define category order and in search results will be sorted for this order and… >>> More

Related posts about assignment

PLPGSQL array assignment not working, "array subscript in assignment must not be null"

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello there, When assigning mobilenumbers to a varchar[] in a loop through results it gives me the following error: "array subscript in assignment must not be null" Also, i think the query that joins member uuids, and group member uuids, into one, grouped on the user_id, i think it can be done… >>> More
Matlab matrix replacement assignment gives error

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, when i tried to update some part of a matrix, i got the following error message: ??? Assignment has fewer non-singleton rhs dimensions than non-singleton subscripts My code tries to update some values of a matrix that represent a binary image. My code is as follows: outImage(3:5,2:4,1) =… >>> More
MATLAB matrix replacement assignment gives error

as seen on Stack Overflow - Search for 'Stack Overflow'
I tried to update some part of a matrix, I got the following error message: ??? Assignment has fewer non-singleton rhs dimensions than non-singleton subscripts My code tries to update some values of a matrix that represent a binary image. My code is as follows: outImage(3:5,2:4,1) = max(imBinary(3:5… >>> More
How can I return default at loop end in Scheme?

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm trying to implement back-tracking search in Scheme. So far, I have the following: (define (backtrack n graph assignment) (cond (assignment-complete n assignment) (assignment) ) (define u (select-u graph assignment)) (define c 1) (define result 0) (let forLoop () … >>> More
Beginning Java (Working with Arrays; Class Assignment)

as seen on Stack Overflow - Search for 'Stack Overflow'
I am to the point where I feel as if I correctly wrote the code for this homework assignment. We were given a skeleton and 2 classes that we had to import (FileIOHelper and Student). /* * Created: *** put the date here *** * * Author: *** put your name here *** * * The program will read information… >>> More