Thread: 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
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 L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
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