I am looking to emulate a feature in Excel/Google Sheets where there is a table of data, but someone may move a row up or down. Here is an example of the interaction:

My first thought as to how to store this in a database table would be by storing an unsigned INT4 with the row position. For example, if the table has two fields, name and age then the table would have the following structure:

`row_num`   `name`    `age`
 1           tom      10
 2           angel    20

And then if, for example, the 2nd row was moved before the first row, the row_num would be changed for both of those. However, I’m wondering what might be a more efficient way to do this. Suppose I have a billion rows and I move the second-to-last-row to the second-to-first: all 1B of the row_nums would need to be updated. I suppose doing an UPDATE statement such as the following might be one way to do this:

UPDATE myTbl row_num=CASE 
                        WHEN $moved_selection...
                        WHEN $changed_section...
                      ELSE row_num END

But I’m wondering if there might be a better way to do this?