If you use Hibernate and ordered list, you’re probably familiar with having to add a column to retain the order. If you don’t want nulls in your list, you sometimes need to reorder this (say, for instance, if you manually remove an item from a list via the database).
Of course, you can do an update to “set ord = ord – 1 where ord = <first gap>” and continue doing so until you have the items nicely sequenced again. But if you have Oracle*, you can use the row_number() feature
Say you have a table ‘someTable’ and you removed a few rows from the list that is grouped by a foreign key (some_fk_id) for 123456 and you want to renumber the ORD field based on the order of inserted ID’s, you can do the following:
1 2 3 4 5
MERGE INTO someTable T USING ( SELECT id, (ROW_NUMBER() OVER (ORDER BY id ASC) - 1) rn FROM someTable WHERE some_fk_id = 123456 ) R ON (T.id = R.id) WHEN MATCHED THEN UPDATE SET T.ord = R.rn;
This will then reorder the list, starting with 0 for that group. I’m pretty sure you can write your cursors to renumber entire groups of lists
* I believe other databases have this feature as well, such as Microsoft SQL Server, however, I don’t use those, so don’t blame me when it doesn’t work there 😉