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_num
s 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?