
User-defined Order in SQL - grzm
https://begriffs.com/posts/2018-03-20-user-defined-order.html
======
cthor
Having run into this problem a few times now, Approach 1 is unfortunately
best. Two reasons:

(1) If updates fail, you don't get a resulting broken state

(2) The size of the lists is not that large (<50), so the extra writes are not
that expensive.

Essentially, with any variation on fractional updates (where only 1-2 rows are
updated per list update), the client is sending to the server "move item a to
position x". Then, shortly after, "move item b to position y", etc. And if any
intermediate step fails, the server and client are out of sync.

If, instead, the client message is "here is the new state of the list:
(a,b,c)" and the server updates, failed requests only leave the state
temporarily out of sync. The client will continue to send the complete correct
state at each update.

~~~
zeroimpl
I've run into this problem when keeping a single large (100000+) list sorted.
In my case it was very important not to change the sort value on rows that
didn't need to.

I had considered most of the solutions listed in the article except for the
true fraction method. I believe that solution was explored by the author as
they thought floating points were somehow solving a problem that couldn't be
solved by integers. I settled on an approach that is essentially Solution 2
but using integers instead, each time bisecting the other two integers. The
important aspect is that when inserting at the head or tail of the list, you
bisect between MININT (-2^63) or MAXINT (2^63-1) respectively.

~~~
chii
have you considered using a string and have them sort lexographically? To
insert, you have to create a new string that sorts between the two, which this
library proposes an algorithm for:
[https://github.com/fasiha/mudderjs](https://github.com/fasiha/mudderjs)

~~~
godshatter
This was my thought, too. Use a point-based versioning-like scheme as a string
(1.1.25, 1.1.26, 1.1.26.1, 1.2, etc). You will have to worry about adding
prefixed 0s, though, as powers of ten are increased if you want it to sort
correctly, and of course there is presumably a hard-limit on the size of the
string.

It might be possible to have a table that keys to the first that has a level
and a value. For 1.1.26.1 above, it would have the rows (1, 1), (2, 1), (3,
26), (4, 1) (with keys to the table rows). Not sure exactly how to write the
sql to join it all together and sort it, but it seems possible, at least if
you have some known maximum number of levels.

~~~
chii
if you use the full unicode space as alphabets, you can probably get a lot of
mileage out of just having a 255 char column (or bigger, if expecting a large
number of items constantly changing). Most databases will have a inbuilt way
to lexographically sort text, and so you won't have to do anything extra. The
smarts is in the computation of the ordering string.

------
arnioxux
> Robust? Yes. It would take virtually forever to run out of space through
> repeated list reordering.

I think the author missed a pathological case with using the Stern-Brocot
tree.

The goal is to sort each item with a score. Then to insert something between
two items, you calculate a new score that is "between" their existing scores.

\- Option 1) Take the average of the two score a/b and c/d:

    
    
      (ad+bc)/(2bd)
    

\- Option 2) Take the
mediant([https://en.wikipedia.org/wiki/Mediant_(mathematics)](https://en.wikipedia.org/wiki/Mediant_\(mathematics\))
):

    
    
      (a+c)/(b+d)
    

We don't want to use the first option, the _average_ because almost by
definition you would require at least another bit of precision in the
denominator for each insert due the multiplication by 2. This limits you to a
meager ~64 repeated inserts before overflowing the denominator.

So the _mediant_ is better because the precision requirements is limited by
the number of times you can add instead of multiply. This might seem to grow a
lot slower at first. And the author even showed that if you're repeatedly
averaging 0/1 with the new score, you would get the pattern 1/2, 1/3, 1/4,
1/5, 1/6, .... Which means you can handle up 2^precision number of inserts
which is much better than before.

But this only applies to inserting to front (medianting with 0/1) or back
(medianting with 1/1) because this will only add a 0 or 1 each time.

If you go down a zigzag path down the middle of the tree instead, you can get
the two numbers added to be much closer in magnitude and explode. For example:
1/1, 1/2, 2/3, 3/5, 5/8, 8/13, 13/21, 21/34, ... (it's fibonacci!).

So essentially you get the same thing as the averaging case where the
precision required roughly doubles(1.6180339887...) at each step and it will
overflow quickly.

This isn't a purely theoretical edge case either. The example from before can
be realized by repeatedly inserting between the last two items inserted, which
arises naturally if you're repeatedly adding to the middle of a list.

~~~
gns24
Exactly. If we're storing the positional value into 64-bits then there must be
some patterns of 64 inserts which don't fit.

The decimal solution is really not bad. You could improve on it by not taking
the midpoint between the two values you're inserting between, but rather
another point which doesn't require an extra digit, when that's possible, but
really that's very likely to be an unnecessary optimisation. So continual
insertion on the end might give 0.5, 0.8, 0.9, 0.95, 0.98 0.99, 0.995...

~~~
arnioxux
That's a really elegant way to put it. Yay for information theory.

------
mci
Vadim Tropashko wrote a book "SQL Design Patterns" that, among others,
presents non-trivial approaches to storing trees and graphs in SQL databases.
I highly recommend it. The PDF version is free here:
[https://vadimtropashko.wordpress.com/%E2%80%9Csql-design-
pat...](https://vadimtropashko.wordpress.com/%E2%80%9Csql-design-
patterns%E2%80%9D-book/about/)

------
hatsuseno
Throwing my qnd solution to this problem in here.

    
    
      id | name | at | date_modified
       1 | foo  | 1  | 2018-03-21 12:00
       2 | bar  | 2  | 2018-03-21 12:01
       3 | baz  | 3  | 2018-03-21 12:02
    

Updating the ordering is a matter of updating a single row with it's new
intended position and triggering a date_modified update.

    
    
      -- Move last item to top
      UPDATE ordered_set SET at = 1, date_modified = NOW() WHERE id = 3;
    

Retrieving can easily be done using a multicolumn order by;

    
    
      SELECT * FROM ordered_set ORDER BY at ASC, date_modified DESC;
    
      id | name | at | date_modified
       3 | baz  | 1  | 2018-03-21 12:10
       1 | foo  | 1  | 2018-03-21 12:00
       2 | bar  | 2  | 2018-03-21 12:01
    

If you want to explicitly get the current ordering as a sequence number some
databases have ROW_NUMBER() OVER (ORDER BY <same as above>) as a potential
solution.

That said, this seems like a pretty trivial non-issue. You wouldn't want to do
this on very large datasets, and updating many rows for small sets performs
just fine in my experience.

~~~
aravindet
If you have two rows with at=1, how would you insert a row between them?

~~~
thedirt0115
You just convert the datetimes to integer timestamps, then find the midpoint
between the two entries... wait this sounds familiar :)

------
aesthetics1
My immediate thought was to store the information in a doubly-linked list type
structure in SQL. Obviously the space efficiency is not optimal, but I am
surprised that the article didn't even point this out.

The table could be designed with a unique PK, the todo item, and some binary
or bool field to determine first (head) and last (tail). You would then add
'previous' and 'next' fields that would be updated for insert/delete/updates.

~~~
lodi
The problem with that is that it kind of breaks SQL to traverse the list.

Let's say you have a 1M element list, and you want to get the first 100
elements. Normally you would write something like:

    
    
      > select top(100) * ... order by itemPosition asc
    

To walk a linked list, you'd either have to:

\- walk it on the client, one query per element, resulting in way too many
roundtrips to the server.

\- get all the data in one go and then walk it on the client. Here we're
selecting and returning 1M records and throwing most of those away.

\- walk the list in the database with a recursive common table expression.
CTE's aren't appropriate for this; it'll be slow, and we'll run up against a
recursion limit for large lists.

\- walk the list in the database with cursors/loops/etc. Very icky, and breaks
composability.

~~~
aesthetics1
You are right and make many succinct points. I imagined this in the context of
the todo list example given in the article, and assumed there would be a sane
limit on items in a list, and that each record in the database would be tied
to a list ID, or user ID, or some other method to allow only pulling the items
that are actually in the list.

That being said, this would not alleviate the problems you mentioned when
walking the list. It would need to be arranged once retrieved either by the
client, or by the backend before passing to the client. I imagined that I
would traverse the list recursively to order it before passing it off to the
client. This should take O(n) time, since we only need to traverse the list
once, and we haven't retrieved from the database any unrelated rows (list ID,
user ID, etc - there has to be some boundary in place).

------
timvdalen
At the start of the article, elegance I defined as:

>The solution shouldn't require complicated PL/pgSQL functions or parsing
lists of numbers in strings.

Not to be overly pedantic, but if creating an extension that implements a new
data type isn't "[requiring] complicated PL/pgSQL functions", I don't know
what is.

~~~
grzm
One of the benefits of Postgres is having user-defined extensions like this
that are effectively drop-in. Yeah, it's a trade-off. The whole submission is
a discussion of the trade-offs between various implementations. One I'd make
is using a custom datatype rather than custom PL/pgSQL functions (and I've
written my share of those, including in this domain—they can be tough to get
right in all of the edge cases) as the type is likely more efficient and
correct.

------
rdiddly
What's it called when the primary key is also a "foreign" (but not really) key
into the same table? Anyway just do that. Each Item has a "PrecededByItemId"
value. To insert X between A & B, just make X preceded by A and B preceded by
X. Or probably better, go the other way (FollowedByItemId). It's the SQL
version of a linked list. Efficient? Yes they're integers and an insert takes
only two changes. Robust? Yes, infinite inserts possible. Elegant? Beats any
of that complicated stuff in the article, unless I'm missing something. I
suppose you could run out of integers but if your to-do list is that long,
well...

~~~
grzm
Would you elaborate on how to select data in the custom order efficiently?
What you're describing can't be easily indexed, if I understand you correctly.
The strategy outlined in the submission ensures minimal table rewrites on
updates (insertion, reordering, deletion) as well as efficient retrieval.

~~~
dzamo_norton

      with recursive todo_list_sorted as (
      	select
      		*
      	from
      		todo_list tl1 where prev_id is null
      	union all
      	select
      		tl1.*	
      	from
      		todo_list tl1
      	join
      		todo_list_sorted tl2 on tl1.prev_id = tl2.id
      )
      select * from todo_list_sorted

~~~
grzm
Yes, there are ways to do it. The question is how to do so _efficiently_.
Recursive queries don't benefit from indexes as efficiently as, for example,
the solution outlined in the submission.

------
kijin
For most practical uses, something like the transaction described in Approach
1 should be good enough.

It has no limit imposed by precision, the benefit of a compact and well-
supported storage format (just one BIGINT) will probably compensate for any
inefficiency caused by updating several rows, and the multiple steps are not
fragile at all if you trust PostgreSQL to handle trasactions properly. If
you're really worried, just increment the sequence _before_ you update a bunch
of rows, not afterward.

But what if you have millions of users reordering billions of todo items?

Well, change the uniqueness constraint to (user_id, list_id, pos) or something
composite like that. Only reorder items belonging to the same list owned by
the same user. If a person is manually reordering items, there can't be too
many items in any given list in the first place. There's no need to touch
billions of other items belonging to other lists and other users.

~~~
grzm
Rewriting even a sublist generates unnecessary updates—on both the table and
associated indexes. (While the article doesn't specifically describe using
sublists, this is likely for pedagogical reasons: he doesn't include surrogate
keys either. The pathological case of a full table rewrite on any update would
be avoided in any event.)

As for storage, the rational datatype described in the submission has the same
size as a BIGINT (64 bits), so there's no storage disadvantage there.

------
gregwebs
Related is modeling trees in SQL, which has been discussed on HN:
[https://news.ycombinator.com/item?id=13517490](https://news.ycombinator.com/item?id=13517490)

------
iffycan
I've had success just using letters between a and z.

\- The first item added has rank "m"

\- If I want to add an item before that, it's m-a/2 (somewhere around "f")

\- If I want to put something between "b" and "c" that's "bm"

\- The database can sort it just fine.

~~~
grzm
This is the "what about leaving room?" variant described in the submission
with either (a) a smaller range of available values if you're limiting
yourself to single characters and/or (b) less efficient storage.

~~~
iffycan
It's less efficient storage-wise, sure, but there's no "occasionally take the
hit of shifting" problem.

~~~
adrianmonk
Your strings could get REALLY long.

Suppose you have 3 items:

    
    
        id  pos  name
        1   'a'  apple
        2   'm'  banana
        3   'z'  cherry
    

You move cherry to the middle of the list. Now, you have:

    
    
        id  pos  name
        1   'a'  apple
        3   'g'  cherry
        2   'm'  banana
    

Now you move banana back to the middle of the list, which gives you this:

    
    
        id  pos  name
        1   'a'  apple
        2   'd'  banana
        3   'g'  cherry
    

Keep doing this, and the gap keeps shrinking. You need to make pos longer to
be more precise to fit in the gap. Eventually you run out of space in your
string field.

~~~
grzm
I read their response as not limiting themselves to a single character (which
you have too, if I read you correctly). If they use a text column, it's
practically arbitrarily large. I believe the limit is around 1GB in Postgres.
They've also dismissed concerns of storage, so it's a price they're willing to
pay.

------
Grue3
Why not strings? For two different strings A and B it's trivial to build one
that is between them in alphabetic order.

~~~
adrianmonk
That is equivalent to the "Arbitrary Precision" section under Approach 2.

------
udioron
Simple solution: Don't use `unique`.

(Let's assume that your db is very fast, and let your app update all needed
positions on save)

~~~
OldSchoolJohnny
Yes! I'm reading through all this discussion and thinking "why are these
people so bent on overthinking such a simple concept"? Also using text as the
sort order solves most of the issues people are bringing up.

~~~
hobs
Pretty much every implementation of arbitrary sort order I have seen uses
varchar for this very reason, and requires none of the cleverness in the
article.

------
adrianmonk
There's an O(log N) data structure for this. Unfortunately I don't know the
name of it.

Keep a (balanced) binary tree where each node contains a pointer to one of the
items you want to keep ordered. (In this case, each node has the primary key
of one of your database rows.)

Every node also contains an integer which tells the size of that subtree (the
tree whose root is that node).

To enumerate the entire list, do an in-order traversal of the tree.

To find the Nth item in the list, check the left child to see if it contains
enough nodes that your Nth item would be in that subtree. If so, go left. If
not, it's either the current node (if off by exactly one) or you go right. But
if you go right, you must reduce N by 1 to account for the current node and
also reduce N by the size of the left subtree.

To enumerate a range, you basically combine the previous two.

To move an item, just move it in the normal way you'd delete/add a node in any
regular tree, but remember to update the counts stored inside any nodes that
are affected, i.e. all parents/ancestors of any node that is removed or added.
If you delete an internal node, you need to be careful to preserve the order
of everything, but there are ways to do that by exchange

This data structure can definitely be modeled in database tables. Whether the
operations on it can all be expressed in SQL is an interesting question. It
might be impossible, or it might just be really tricky.

------
georgekronberg
While it's vendor-specific, SQL Server has a built-in solution for it:
[http://technet.microsoft.com/en-
us/library/bb677173.aspx](http://technet.microsoft.com/en-
us/library/bb677173.aspx)

~~~
z92
It doesn't do what I supposed it would do.

> A column of type hierarchyid does not automatically represent a tree. It is
> up to the application to generate and assign hierarchyid values in such a
> way that the desired relationship between rows is reflected in the values.
> Some applications might have a column of type hierarchyid that indicates the
> location in a hierarchy defined in another table.

------
AprilArcus
why not use a linked list made of self references?

    
    
      id | text               | prev | next
       1 | "steal underpants" | NULL | 2
       2 | "???"              | 1    | 3
       3 | "profit"           | 2    | NULL
    

For the use case of rearranging and inserting at arbitrary positions, wouldn't
a linked list be ideal?

~~~
bfung
For the query to retrieve the items back in order, logically it goes like:

    
    
       select * from (items) order by `prev`  --`next` doesn't help
       -- also hope that NULL is at the beginning
    

So it's no better than Approach 1. in the article, but is actually worse with
the extraneous column in storage and head scratching for the next maintainer
of the system.

Exercise for the reader: what happens when item id #3 is moved in between item
id #1 and #2, how many update statements are needed?

~~~
dzamo_norton
No, this query doesn't retrieve the items in the correct order in general. It
only works for the particular data in GP because the row ordering coincides
with the user's ordering there. Try ordering by `prev` on the table below,
which is a different valid representation of "steal underpants" -> "???" ->
"profit".

    
    
      id | text               | prev | next
       1 | "profit"           | 2    | NULL
       2 | "???"              | 3    | 1
       3 | "steal underpants" | NULL | 2

~~~
bfung
Yep, that's fine - highlights the complexity of implementing the ordering in a
linked list. The fundamental thing is that both columns aren't needed, only
one column is needed to determine the order.

------
Svip
I must admit, I don't like the the use of floats/decimals/rationals in this
way. They are unfit for sorting.

Say I wanted to present a list of users with their top three to-do items,
that's impossible using the above method.

    
    
        select 
            u.user, item1=i1.text, item2=i2.text, item3=i3.text
          from
            users u
              join items i1 on i1.user = u.user and i1.pos = 1
              left join items i2 on i2.user = u.user and i2.pos = 2
              left join items i3 on i3.user = u.user and i3.pos = 3
    

Them being integers means I can rationally reason about them. I can also infer
their position easily. With floats, I don't know where pos=3 is without
counting how many have a lower number.

Floats only solve two issues: Making inserting easier while still being able
to use `order by`. But reasoning about your data becomes a lot harder.

~~~
trinitry3
> Say I wanted to present a list of users with their top three to-do items,
> that's impossible using the above method.

Not really:

    
    
        SELECT u.user, i.text
        FROM users u,
        LATERAL (SELECT * FROM items WHERE items.user = u.user ORDER BY items.pos DESC LIMIT 3) i;
    

(You can also make these columns instead of rows, but as someone already
pointed out, you'd usually not do that in SQL.)

~~~
Svip
You are not wrong, although your example would not work in every SQL dialect
(even when rewritten). Sybase's T-SQL does not have equivalent for LIMIT.[0]

My point being, there are some issues with regards to data purity and
reasoning, particularly if you want a system where users can build custom
reports.

And I just wish the original article highlighted that using floats _would_
have these issues, and if writing SQL like this is important to you, then you
might want to reconsider.

(As for your note in brackets, where I work, SQL is basically used as a
scripting language. So we would do my example in SQL.)

[0] Yes, I know there is 'set rowcount', but you cannot do that within a sub
select.

~~~
smnc
> Sybase's T-SQL does not have equivalent for LIMIT.[0]

Are you looking for SELECT TOP 3 ...?

~~~
Svip
First available from 12.5.3. And I cannot be certain we have that version or a
newer running.

But you are right, I should have clarified that.

------
linkmotif
I encountered this problem on what must have been the first web programming
project I ever tried and it’s haunted me since. I still think all the time
about ways to solve this issue. It crops up all over the place.

I’ll never brush aside the SQL-object impedance mismatch like many people do,
as something to work around in an otherwise great system that you can adapt to
anything. SQL and relational modeling is an abstraction that leaks way too
easily when it comes to these sorts of “human” use cases, wherein you want to
do something that’s not perfectly rectangular like arbitrary sort. SQL
probably didn’t leak like this when used for enterprise invoice-orders that it
was designed for, but using it for applications where a touch screen is the
primary user input that motivates the model: I’m not so sure it holds up.

------
tabtab
Another approach is that users often want the newest items listed first, but
occasionally want exceptions. Thus, have a "sort date" with a time portion
that governs the order. The sort-date defaults to the data entry (add)
date/time. The display date and sort-date may differ, if present. A sort-date
doesn't work for everything, but is simple, intuitive, and effective for news-
or blog-like content.

------
blaisio
This is pretty cool!

Honestly I don't think there is a "best" solution here. Even in a "regular"
programming language where you can easily use any data structure, it's not
obvious which is best for maintaining the order.

Updating the order "value" of all the rows = moving a value around in an array
(causing you to also shift a bunch of other values around).

Storing the ID of the row that should show up next in the list (not mentioned
in the article, but mentioned in the comments below) = moving nodes around in
a linked list.

Creating a new index that will fit in between two existing rows = inserting a
value into a tree (because of the index).

It is a little bit trickier in a database, just because, no matter what you
do, you also have to pay the price of putting something into a tree as well.
Still... I can think of situations where I'd use any of these techniques. In
general, this is not a problem with a clean solution for every use case.

------
jparise
At Pinterest, we expanded the precision of our existing timestamp-based
sequence column to accommodate reordering:

[https://medium.com/@Pinterest_Engineering/how-we-built-
rearr...](https://medium.com/@Pinterest_Engineering/how-we-built-rearranging-
pins-b11052e95c8b)

------
fiatjaf
What about storing all the item ids in a text array (on another table, one
related to these items somehow)?

    
    
      int[]
    

If the items are being ordered manually by the user, then they cannot be of a
very large length.

~~~
grzm
That may meet your needs. You’ll lose some of the benefits of database
constraints and indexing.

And while the submission uses it as an example, the use case of reordering
items in a list needn’t be limited to lists manually ordered by a user.

~~~
fiatjaf
It's not "an example", it's the entire point of the article.

------
Animats
Your to-do list is how big?

------
mf2hd
I have the same problem right now.

I use an unsigned int "pos" column in the db.

For a new list the "pos" starts with 1..n

When the user changes the order I find the largest "pos" in the list and with
a for I set largest + n "pos" for every item.

There will be about 20 items in a list but let's just assume it's a 1000, so
with this I'm good if the user edits the list less than 4 294 967 295 / 1000
times.

And if the largest + count(list) < lowest I can set the largest to zero and
start over the pos with 1.

------
flomo
I implemented something like this in a naive way. I took the modified date
(previous default sort order) and stored the unix timestamp * 100 in a 64 bit
indexed SortVal column. Drag-n-drop sets the SortVal +/-1 relative to the item
it is being dropped against. There's only ~30 items per page so I figure it
won't break except in the pathological case.

Curious if there is some obvious flaw with this plan or if I really need the
power of true fractions.

~~~
gluxon
How do you handle situations where two items (lets call them A and B) are both
moved before a third item C?

Then wouldn't A and B both have a "SortVal" that's 1 less than C's SortVal?
That would make your SortVals non-unique and order arbitrary.

~~~
jve
Well, only if your UI allows dragging two items at once. If you reorder
multiple items, well, I suppose you can resolve it by passing some relative
position. If you want to squeeze something in-between reordered items, then -1
probably is not a good choice, but more like calculating some middle value
between previous and next item.

~~~
grzm
What would the timestamp represent? Are you providing a timestamp which
encodes the position somehow, or using CURRENT_TIMESTAMP? Updating the
timestamp for all of the rows in the (sub) list? How does this permit you to
reorder the items in the list?

~~~
jve
I haven't implemented this, just brainstorming.

Timestamp would only represent position and enables to put something in
between, rather than an integer which adds +1. If put A (timestamp = 100)
between B (timestamp = 200) and C (timestamp = 300), just set that order value
to middle (150). Naive

It actually looks the same as "What about leaving room?" and, yeah, introduces
complexity when you organize items for too long.

------
garyclarke27
Interesting, thanks for posting,I wish Postgres would add a native rational
type to core, would be far more useful than the umpteen other esoteric types
they have. How battle tested is your rational number datatype? I just use 2
columns for fractions and some udf’s, is there a huge advantage with a single
column? Did you look at the pgmp extension includes !mpq rational type based
on GMP library? Looks v powerful but License put me off.

------
zamalek
You can also use ORDPATH[1], which is built for tree structures but should
work just as well for a flat list as it does support "in-between inserts."

[1]: [http://www.dbis.informatik.hu-
berlin.de/fileadmin/lectures/W...](http://www.dbis.informatik.hu-
berlin.de/fileadmin/lectures/WS2011_12/NeueKonzepte_Vorlesung/ordpath.pdf)

------
dizzystar
There are a lot of solutions to this problem, each with their own merits.

Personally, I'd probably add a second table with FKs to the todos table PK.
That allows you to remove the calls to nexval. It doesn't remove the need for
a processing language, but it's a much cleaner solution, I think.

------
wiz21c
The problem of swapping two entries in such a list is also quite funny.

------
chmaynard
The author has imposed some rather arbitrary constraints on possible
solutions, probably for pedantic reasons. I'd prefer to look for ways to use
an index.

------
yoz-y
What about using linked lists? If the write performance is more of a concern
than read performance then it is a possible option too.

~~~
FLUX-YOU
It's cheaper to write to the DB when doing ordering updates, but you lose easy
'order by sort_order' in queries since the nodes you're linking to will likely
be primary keys that are in a different order than the creation sequence. This
probably has index ramifications but I don't know enough about them to say.

That's fine if you sort everything on the front end though. It's product-
dependent.

Like someone else said, usually these kinds of lists are small enough and
PCs/databases are fast enough for it to not really matter.

------
ape4
Use a blob. Do the reordering in the program. Write the todo list (in order)
into a blob with some delimiter between each item.

Pro: simple

Con: might as well use a flat file

------
Scarbutt
And with a graph database this would be trivial.

~~~
jimktrains2
You say that as if it solves all problems (e.g. fast reads) at the same time.

You could very easily build a link-list type datastore in SQL and use a CTE to
build the list, but if your main use-case is viewing the list rather than
updating the order, then it's a slower compromise than something like the
solutions OP was discussing.

------
legec
arrays ?

------
some_account
I think I would simply use json to store the list in a field. Makes it very
easy to read and replace the json but then again, I would not be using SQL for
this so I'm cheating.

~~~
repsilat
It depends a lot on how many items are in the list. If there are less than
10000 items in each sorted set you should probably let the client handle the
ordering and just do bulk inserts with order index in a denormalised JSON col
or something, but after that you probably want something db-native.

~~~
joshribakoff
What is the use case where your app would need to let the user sort such a
large list? I cannot think of any use case. At that point I would let the user
set the order field (integer) as it would not be feasible to have a drag and
drop UI anymore. That would be cumbersome to use.

~~~
mnsc
It's not 10k but I have Spotify playlists with 4k+ tracks (queue of new albums
I listen to as radio at the office) and I always fiddle with the order of the
albums at the tail of the playlist.

