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.
Since this is a transactional database, why not simply UPDATE … SET at = at + 1 WHERE at >= new_at & then INSERT? Things like this usually aren’t insanely write-heavy.
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.