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 = ” 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:

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 ;-)