CODEWITHSUNDEEP
sqlsorting
Jabberwocky
Jabberwocky

Reputation: 50831

Storing data in a user specified order

I know that in SQL databases data is not stored in a particular order and that ordering is only done via ORDER BY statements.

What is the usual approach if I need to store data in a arbitrary order?

Example:

I have names and I want to store them in a certain arbitrary order like:

name
-----
foo
hello
bar

One approach would be to have an auxiliary field order like this:

order  name
-----------
1      hello
0      foo
2      bar

then I can sort by the order field.

But if I need to insert another name (newname) that should be between foo and hello I need to change the order fields of hello and bar so the table would be this:

order  name
-----------
2      hello
0      foo
3      bar
1      new

Upvotes: 2

Views: 294

Answers (1)

Mureinik
Mureinik

Reputation: 311563

As you noted, renumbering the entire table when you have a new item is probably not a great idea.

One common practice is to keep sufficient gaps in the numbering values so you can insert new values between them. E.g., if you start off with 0, 100 and 200, the new value can easily be 50.
Note that in theory, you could also use floating-point numbers which would give you the ability to insert infinite new values between each value. In practice, the precision the database can store these floating point numbers is limited (depending on the RDBMS and the column definition).

Upvotes: 1

Related Questions