Обсуждение: list ranking

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.

Thanks
Jodi

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