Manage Records Ordering

Manage records ordering

T-SQL's ROW_NUMBER() operation might be useful to you in terms of consolidating order values.

This is an example of how you can use this functionality to consolidate your order values without affecting the ranking of items within that order:

/* declare placeholder and populate with test values */
declare @MyTable table (ID bigint identity(1,1), [Order] bigint)

insert into @MyTable ([Order]) 
VALUES
(1),
(3),
(2),
(5),
(-4),
(13),
(0)

/* Look at values we've just inserted */
select * from @MyTable order by [Order]

/* Show how ROW_NUMBER() can apply a consolidated ranking based on our existing order */
select *, ROW_NUMBER() over (order by [Order] asc) as sort from @MyTable

/* Apply that consolidated ranking to update the order values */
update @MyTable
set [Order] = consolidated.sort
from 
(
select ID as refID, ROW_NUMBER() over (order by [Order] asc) as sort from @MyTable
) consolidated
where consolidated.refID = ID

/* Final display of updated table */
select * from @MyTable order by [Order]

Ideally, though, it's worth taking some time to manage and keep clean the data from the start.

Part of this is database structure and normalization, looking at things like:

• Can a record exist and not be ordered?
• If there is more than one user, do they all use the same order, or would each user have a separate order ranking?
• Is it possible a user might have more than one ordering that they
  want to use and switch between?

If any of those are true, you might want to break the ordering out into a separate table for data integrity purposes (and because of transaction locks and things, it might be worthwhile to do even if you don't)

Database design aside, it's also worthwhile to look at how you handle the data operations for modifying record order.

If we're using a table with N records, and these records have a densely-packed order (as per what the query above demonstrates, where the order values are 1,2,3,4,5,etc.), then any time we make a change to that order, we have to update a lot of the existing order values in the table.

For example:

• The user modifies a record so its order changes from 5 to 2. We now have to shift every record from order 2 onwards to the end of the order.

One way to compensate for this is to calculate ranking using offsets - rather than ordering by 1,2,3... instead use larger values like 10,20,30.... This lets you handle the user's ordering changes without a lot of immediate database load (move something up to order 2? Slot it in at position 15, between 10 and 20), and you can then optimize the ordering later.

How to keep ordering of records in a database table

I feel this is related to the general problem of using an array vs a linked list.
How about storing a foreign key referencing the next record in the same table? This is the linked list like approach.

For your example there are not too many tabs so an array based approach should work fine.
But for someone having hundreds of records it may be useful to use a self-referential foreign key.

ID Name      NExT  
 1 Home      2  
 2 About     3  
 3 Products  4 
 4 Shopping  NULL

Adding and deleting rows will be akin to linked list insertion and deletion.

Update:
Modified table

ID Name       NExT  
 1 Home       5  
 2 About      3  
 3 Products   4 
 4 Shopping   NULL
 5 Contact us 2

The order will be 1 > 5 > 2 > 3 > 4 as determined by the next column.
You can also use a prev column which will make it similar to a doubly linked list.

How to implement re-ordering of CoreData records?

FetchedResultsController and its delegate are not meant to be used for user-driven model changes. See the Apple reference doc.
Look for User-Driven Updates part. So if you look for some magical, one-line way, there's not such, sadly.

What you need to do is make updates in this method:

- (void)tableView:(UITableView *)tableView moveRowAtIndexPath:(NSIndexPath *)fromIndexPath toIndexPath:(NSIndexPath *)toIndexPath {
 userDrivenDataModelChange = YES;

 ...[UPDATE THE MODEL then SAVE CONTEXT]...

 userDrivenDataModelChange = NO;
}

and also prevent the notifications to do anything, as changes are already done by the user:

- (void)controllerWillChangeContent:(NSFetchedResultsController *)controller {
 if (userDrivenDataModelChange) return;
 ...
}
- (void)controller:(NSFetchedResultsController *)controller didChangeObject:(id)anObject atIndexPath:(NSIndexPath *)indexPath forChangeType:(NSFetchedResultsChangeType)type newIndexPath:(NSIndexPath *)newIndexPath {
 if (userDrivenDataModelChange) return;
 ...
}
- (void)controllerDidChangeContent:(NSFetchedResultsController *)controller {
 if (userDrivenDataModelChange) return;
 ...
}

I have just implemented this in my to-do app (Quickie) and it works fine.

Problem sorting, grouping, and re-ordering a selection of records

I think you want:

SELECT DateID, Series, ClassStart
FROM Classes_Dates
WHERE ClassID = 11
GROUP BY Series, ClassStart, DateID
ORDER BY MIN(ClassStart) OVER(PARTITION BY Series) 
       , ClassStart

Note that if the (Series, ClassStart, DateID) is a unique key in this table, then you don't even need the GROUP BY:

SELECT DateID, Series, ClassStart
FROM Classes_Dates
WHERE ClassID = 11
ORDER BY MIN(ClassStart) OVER(PARTITION BY Series) 
       , ClassStart

And just to catch the (probably rare) case where two Series have the same MIN(ClassStart), you should use this one so data from these two Series don't get mixed up in the results:

SELECT DateID, Series, ClassStart
FROM Classes_Dates
WHERE ClassID = 11
ORDER BY MIN(ClassStart) OVER(PARTITION BY Series) 
       , Series
       , ClassStart

---

How the query works:

What your problem describes is that you want the data shown in groups (of same Series). But you also want these groups ordered depending on the MIN(ClassStart) of every group.

To find MIN(ClassStart) we'd have to use GROUP BY Series but we can't do that because then the muptiple rows (of same group) would collapse into one.

This is what MIN(ClassStart) OVER(PARTITION BY Series) achieves. It calculates the minimum of ClassStart as if we had used GROUP BY Series.

What's the best way to store sort order in SQL?

None of the answers so far have touched on the real problem with custom sort order and that is what happens when two different people want the same records sorted differently.

If you need a custom sort order, you need a related table to store it in, not an additional field. The table would have the userid, the recordId of the data and the sort order for the record. That way Joe Smith can have one order and Sally Jones another for the same data. Now you have the problem of new records being added to the data set. Do you put them at the beginning of the sort order or the end or do you require the person to set an order for them before they can be added to the set. This is in actuality a very complex problem that is generally not worth the amount of time it takes to implement because almost no one ever uses that system once it's in place (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated in terms of saving the order of all the records (which will of course require changes the next time the query is run since there will be new records.) This is very painful process of limited untility.

I did this once in a proposal writing application because we needed to be able to sort the parts and tasks on the proposal in the order we thought would be most impressive to the customer. Even then, we had to institute a default order, so that they only need to move around the two or three things they really wanted to show up first instead of ordering 10,000 individual parts.

A better choice if you can get them to buy off on it, is to allow them to sort the data by columns (desc or asc). Usually the user interface can be designed so that if you click on a column header, it will resort the data by that column. This is relatively straightforward to do and meets most needs for custom ordering.

You really need to discuss this requirement with management and get details of how they want it to work beyond, I want custom ordering. This is often one of those things people think they want, but don't really use.

Reordering an ordered list

Use the 'orrible old trick made famous(?) by old BASIC coders - set your orders to be 100, 200, 300, 400 etc. and then you can pick an order 'inbetween' when you need to. This could get messy - and if you're anticipating a lot of reordering then I'd recommend that you have a scheduled task to 'reorder' the order values every now and then for the entire table.

Moving and re-ordering records permanently

You can use a common table expression (CTE) to perform the UPDATE operation. This is how your query could look like for the 'After' case:

;WITH ToUpdate AS (
  SELECT ID, Name, ItemOrder,
         ROW_NUMBER() OVER (ORDER BY temp_order) AS NewOrder
  FROM (
  SELECT ID, Name, ItemOrder,
         CASE 
            WHEN Name IN ('Albania', 'Barbados') THEN x.o + ItemOrder * 0.1 
          ELSE ItemOrder
         END AS temp_order
  FROM mytable 
  CROSS JOIN (SELECT ItemOrder FROM mytable WHERE Name = 'France') AS x(o)) AS t
)
UPDATE ToUpdate
SET ItemOrder = NewOrder

The trick is to get France's order number and add a fraction of the order of 'Albania', 'Barbados', so that:

• 'Albania', 'Barbados' are positioned after 'France'
• 'Albania' and 'Barbados' maintain the order between them
• The order of the records following 'France' is also maintained.

Demo here

Edit:

The 'Before' case can be implemented if we substitute this line:

WHEN Name IN ('Albania', 'Barbados') THEN x.o + ItemOrder * 0.1 

with this line:

WHEN Name IN ('Albania', 'Barbados') THEN x.o - 1.0/ItemOrder * 0.1  

How can I insert records from a table to another table order by the specific column value?

It seems that record_id is your primary key and thus default ordering is done by that. That's why your output is same as in tableA. Just use ORDER BY in SELECT clause for tableB.

---

Related Topics