custom sorting order in a table

Question

I have a table with a list of user names, how do I implement a custom sort order for the following scenarios.

create table User (name varchar(255))

e.g. User {John, Sam, Mike} the order could be re-arranged in the UI using the Up / Down buttons to {Sam, John, Mike} or {Sam, Mike, John}

Do I need to add a separate column to the table (e.g. sortOrder - integer data type) to take care of this sorting.

So that, I can set the sortOrder information on all rows every time there is a modification in the UI. The drawback with this mechanism is that every time there is a small modification, the sortOrder column needs to be re-written for the affected rows.

We also tried setting modified_date (date type) as an alternative to the sortOrder column.

Is there a better way to acheive this? Appreciate any help on this topic

Answer 1

Yes, I think adding a "SortOrder" column is probably the most efficient and most flexible solution to your challenge here. Anything else will always depend on some other sort criteria - alphabetically or some e.g. date or something, but won't give you the ability to get any arbitrary sorting done, really.

Use a SortOrder column! It'll work perfectly fine for most cases!