Data schema for storing arbitrary order

Currently this is purely hypothetical, though I'm semi-considering something
like this for part of a current project. I'm wondering how to apply a
pseudo-order to a set of data.
To give an example, if you were storing Service Ranks, which were
referenced by the service personnel you stored in the database :-
(... indicates other ranks in-between)
Rear Admiral
...
Admiral
...
Captain
...
Ensign
You store the data as a 2 column table, RankID (unique system-generated eg
Identity(1,1)) and RankName (varchar(50) perhaps).
So you now want to produce a report, that lists the personal in order from
highest rank to lowest rank. As far as the software user is concerned, if a
rank was missing, they would just enter it, tell the system what rank it was
below/above, and it would be stored. But how best to store the list in the
database, given that a new item could be inserted anywhere in the list? You
can't sort alphabetically, because the ranks aren't alphabetically ordered.
You can't trust an auto-generated ID, because that would put most recent
additions at the bottom, earliest at the top.
My current thought is to add a new column to the items - RankPos (integer) -
with an arbitrary (x) between the items (so first rank would be 10, fourth
rank down would be 40, the rank below it would be 50 etc)... if a rank is
inserted between the two, I'd give the new rank 45, and then run an update
once in a while (or if the numerical gap was used up) that would re-number
this column so there was an gap (x) of 10 again. But, is there a better way?
As I said, just wondering at the moment, but not the first time. I seem to
be stuck in the record groove, coming back to the above way each time I
think about it.
Yours,
Ann-Mariethat is one way....to add a "SortOrder" or "SortSequence" column
the other way is to implement this as a linked list or dual linked list.
Where each individual record keeps track of who is above and who is below
them.
just food for thought.
Greg Jackson
PDX, Oregon|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e7VFu98LFHA.2796@.tk2msftngp13.phx.gbl...
> that is one way....to add a "SortOrder" or "SortSequence" column
> the other way is to implement this as a linked list or dual linked list.
> Where each individual record keeps track of who is above and who is below
> them.
> just food for thought.
> Greg Jackson
> PDX, Oregon
>
From the software development background, yes, I would agree. But when it
comes to SQL, but wouldn't I then have a problem doing a simple query to get
the personnel returned 'ORDER BY {rank}'. Echoes of the adjacency tree
storage vs. nested-set tree storage problems and discussion come to mind.
That said, thanks for your reply,
Ann-Marie