I’ve brought it up / asked before about sorting rows manually (i.e. drag & drop), but I want to bring it up again and see if there are any other options, and also to add this as a feature request (albeit very low priority).
What’s your idea?
The ability to sort rows in a table arbitrarily, i.e. by a drag & drop operations. This would work in the Data Viewer, and also provide some GraphQL API to order rows (move row x below row y, move row to top/bottom, etc.). Maybe it would be a feature you can toggle so the extra logic isn’t needed for every table.
What problem might it solve?
Records in a database are read in the same order inserted (depending on engine), but after that, there’s nothing you can do to change it except implement custom ordering. It’s very useful not having to introduce a sort order manually–finding the best solution, implementing it, the extra logic. In code, you simply use an “ordered dictionary”. Done. DB systems are dumb in this regard, while that is not their job, it’s an big inconvenience.
Have you seen it somewhere else?
In a perfect world, there wouldn’t be this mismatch between programming language data structures and database data structures. For example, in MS SQL there is the notion of hierarchical data (https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15), and when lots of data is naturally represented this way, it makes working with a DB so much easier. With programming data structures, it’s not even something you think about, you just do it.
Any ideas on how you think it could/should work?
Using arbitrary precision decimal numbers, and inserting “in between” such as adding 1.5 between 1.0 and 2.0, inserting 1.25 between 1.0 and 1.5. If you can’t have arbitrary precision, then some kind of normalization needs to be done. Another approach I’ve read is using 1…MAX_INT and using (position / count) * MAX_INT to space out the numbers so far that a conflict is rare… but can still happen in large data sets.
Other options are storing the sorting in another table, using “Allow Multiple”, and re-writing the field after sorting it. The problem with this is pagination–you can’t sort the data until it’s on the client. Another option is arbitrary length strings, in reverse order as you can insert ‘zz’, which will be ordered before ‘z’:
Previous post about ordering records: