Re: list ranking

Jodi Kanter wrote:
> We have a set of records in a table that needs to be ranked. We thought
> of adding a ranking field, but the problem is that the ranking could
> change often and there could be additions and deletions from the list.
> Updating this ranking field is quickly going to get messy.
>
> Is anyone familiar with link surgery? Can we do this with a doubly
> linked list? Is there a standard database solution for doubly linked
> lists? or another way to solve this that I don't see?
> Any suggestions on structure would be greatly appreciated.

It sounds to me like you just need a way to enforce a sort order that is
able to handle insertions into the middle of the list? What about
something like this:

create table foo(f1 int, f2 text);
create unique index foo_idx1 on foo(f2);
insert into foo values(1,'0000');
insert into foo values(2,'0001');
--
-- insert new values in between '0000' and '0001'
insert into foo values(3,'0000.0000');
insert into foo values(4,'0000.0001');
--
-- we happen to need one right between those last two
insert into foo values(5,'0000.0000.0000');
--
-- now get them back in rank order
regression=# select * from foo order by f2;
  f1 |       f2
----+----------------
   1 | 0000
   3 | 0000.0000
   5 | 0000.0000.0000
   4 | 0000.0001
   2 | 0001
(5 rows)

You might want to periodically run a maintenance script that collapses
the segments (i.e. turn f2 above into '0000', '0001', '0002', '0003',
and '0004').

HTH,

Joe