How Keep Data Don't Sort

How To Prevent Automatic Sorting On Primary Key in SQL Server

So there are two factors here which you may be confounding; the physical ordering of the data (i.e how your computer literally prints the bits onto your hard drive) and the logical ordering.

The physical ordering is always defined by your clustered index. 9 times out of 10, this also happens to be your primary key, but it doesn't have to be. Think of "Clustered Index" as "How it's physically sorted on my disk". You want it stored a different way, change how you "cluster" it (by the way, if you don't have a clustered index, that's called a "heap", and it doesn't guarantee the physical order of the data).

The logical ordering however, does not exist. SQL is based on relational algebra, and relational algebra operates on mathematical sets. The definition of a set contains no information about the inherent ordering of the set, only it's constituents.

Now when you query data out, you can specify an order by which will return the data to you in the order you asked for. However by default, there is no guarantee or assumption of any ordering (even that of how it's physically stored on disk; even if it happens to return in that order from time to time).

Prevent re-sort on data change or while row is being edited

A bit late to the game, but I'm posting a response now in the hope that it helps others.

Here is a link with a good answer:
https://react-table.tanstack.com/docs/faq#how-do-i-stop-my-table-state-from-automatically-resetting-when-my-data-changes

TLDR: add these to your useTable args list:

        autoResetPage: false, 
        autoResetExpanded: false,
        autoResetGroupBy: false,
        autoResetSelectedRows: false,
        autoResetSortBy: false,
        autoResetFilters: false,
        autoResetRowState: false,

Hope this helps :)

Is there a way to disable initial sorting for jquery DataTables?

Well I found the answer set "aaSorting" to an empty array:

$(document).ready( function() {
    $('#example').dataTable({
        /* Disable initial sort */
        "aaSorting": []
    });
})

For newer versions of Datatables (>= 1.10) use order option:

$(document).ready( function() {
    $('#example').dataTable({
        /* No ordering applied by DataTables during initialisation */
        "order": []
    });
})

Is there a way stop table from sorting in R

One way to get around this is...don't use table. Here's an example where I create three one-line data sets from your data. Then I read them in with a descending sequence, with read.table and it seems to be okay.

The real big thing here is that multiple data sets should be placed in a list upon being read into R. You'll get the exact order of data sets you want that way, among other benefits.

Once you've read them into R the way you want them, it's much easier to order them at the very end. Ordering of rows (for me) is usually the very last step.

> dat <- read.table(h=T, text = "id nobs
  1 25  463
  2 26  586
  3 27  338
  4 28  475
  5 29  711
  6 30  932")

Write three one-line files:

> write.table(dat[3,], "dat3.csv", row.names = FALSE)
> write.table(dat[2,], "dat2.csv", row.names = FALSE)
> write.table(dat[1,], "dat1.csv", row.names = FALSE)

Read them in using a 3:1 order:

> do.call(rbind, lapply(3:1, function(x){ 
       read.table(paste0("dat", x, ".csv"), header = TRUE) 
  }))
#   id nobs
# 1 27  338
# 2 26  586
# 3 25  463

Then, if we change 3:1 to 1:3 the rows "comply" with our request

> do.call(rbind, lapply(1:3, function(x){
      read.table(paste0("dat", x, ".csv"), header = TRUE)
  }))
#   id nobs
# 1 25  463
# 2 26  586
# 3 27  338

And just for fun

> fun <- function(z){
      do.call(rbind, lapply(z, function(x){ 
          read.table(paste0("dat", x, ".csv"), header = TRUE) }))
  }
> fun(c(2, 3, 1))
#   id nobs
# 1 26  586
# 2 27  338
# 3 25  463

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.

Should I store table's current page and sort order in React's state?

The heuristic I use when deciding between using state, and an internal class property goes something like this:

If its part of the public api, where the values are passed in via a parent component or even passed down to children, then use state. e.g. this.state.page
But if its an internal implementation detail, then use a class property following some sort convention. e.g. this.__page