Maintaining sort order of database table rows

Forget correct -- the problem you want to avoid my not be a big deal but, rather, just a couple UPDATE statements depending on your RDBMS (I’m assuming Oracle and that you’re moving the article “up” the list):

UPDATE Articles
SET sort_number = sort_number + 1
WHERE sort_number BETWEEN :new_sort_number and :current_sort_number - 1;

UPDATE Articles
SET sort_number = :new_sort_number
WHERE article_id = :article_id;

The biggest caveat is that the SET functionality doesn’t work the same in all RDBMS.

If you really want to consider correct, consider rethinking the question in terms of data structures – what you might be asking is how to implement a linked list in a database.

An alternative to maintaining your sort number column would be to maintain a parent ID column. This is arguably more correct since it preserves the serialization of your data but the drawback is that querying the data isn’t pretty or efficient (think CONNECT BY in Oracle, for example).

If the question is, instead, what’s best perhaps you want to consider both the parent ID column for correctness and denormalizing the data by deriving a sort number column’s value, possibly from the parent ID data or from the first solution.

If I understand your problem correctly; I would use a column containing some type of ranking index number. This number doesn't have to be unique. You will need to come up with some type of algorithm to calculate the rank.

Then just sort on the ranking column, with a secondary column to handle ranking ties (maybe the create date or something).