Sort order with removable entries

[Axoliien]

I need some help figuring out the algorithm for designating a sort order on objects which are removable from the database. I would normally use integers to denote sort order, however with removable entries this does not seem efficient.

Example: I have a list of projects and want to order them depending on their importance. The projects can be cancelled, thereby removing them from the list. Using integer numbers will leave gaps when a project is removed. A linked list would be the best application, however can this be done in a database?

 

[PHV]

Using integer numbers will leave gaps
And what's the matter ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Darrylles]

Hi,

Why not completely renew your 'integer' sort order values when projects are added/removed?

Surely you need to do this when adding a new project anyway? (The new project may well be priority number 1, when a project is already number 1).

On the other hand, do you have Priority Status '1' for approx. 10 projects, status 2 for approx. 20 projects etc etc?

Or, do you have say 50 projects, each with a unique priority status value? (e.g. only one project with status '1', only one project with status '2', only one project with status '3' etc)?

I assume from your comments - the latter is the case. If so, then every time you add or remove a project - you must re-assign priority status values.

What help do you need? What IS your algorithm rule?

Regards,

Darrylle




Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[willir]

Axoliien

Your issue is missing records and not missing fields / columns. You sort them by priority (+ newest date?). How doe you assign the priority - manually assign a number, or set a list of priority issues used to determin sort order.

It sounds as if you assign a ranking system / sort order number. You can actually sort by the number but not display it on the report or form. If you need to depict urgency, a simple text message and maybe the date would accomplish this.

Richard

 

[Axoliien]

I am trying to design a ranking system. I wrote a simple algorithm on Friday and haven't tried it yet. The idea is that we don't want integer gaps between items PHV, and I am trying for something a little more efficient than completely renewing every integer number. I think I figured it out, here's the basis to the algorithm if you really want to see it. It is actually a lot simpler than it looks, I just wrote out examples to verify correctness.

Purpose: Change an integer defined sort order using an algorithm that can be reused for moving the item from position n to position n-m.  The position m must be a positive or negative integer number.  The number k is defined by the position m (k has the sign of m).

Each item has an item number, a PriorSO number, and a CurrSo number, all of which are positive integer values.

1) Change the CurrSO for item with PriorSO (n) to value (n – m)

2) Change the CurrSO for items with PriorSO [(n - m) to (n – (1 * k))] to value (CurrSO + (1 * k))

3) Change the PriorSO for items with CurrSO [(n – m) to (n)] to value CurrSO

Definition: Let m = 2 (Move up 2), Let k = 1 (since m > 0)
Item | PriorSO | CurrSO
1    | n-2     | n-2
2    | n-1     | n-1
3    | n       | n
4    | n+1     | n+1

1)     Change the CurrSO for item with PriorSO (n) to value (n – m)
Item | PriorSO | CurrSO
1    | n-2     | n-2
2    | n-1     | n-1
3    | n       | n-2
4    | n+1     | n+1

2)     Change the CurrSO for items with PriorSO (n - m) to (n – (1 * k)) to value (CurrSO + (1 * k))
Item | PriorSO | CurrSO
1    | n-2     | n-1
2    | n-1     | n
3    | n       | n-2
4    | n+1     | n+1

3)     Change the PriorSO for items with CurrSO (n – m) to (n) to value CurrSO
Item | PriorSO | CurrSO
1    | n-1     | n-1
2    | n       | n
3    | n-2     | n-2
4    | n+1     | n+1

Definition: Let m = -1 (Move down 1), k = -1 (since m < 0)
Item | PriorSO | CurrSO
1    | n-2     | n-2
2    | n-1     | n-1
3    | n       | n
4    | n+1     | n+1

1) Change the CurrSO for item with PriorSO (n) to value (n – m)
Item | PriorSO | CurrSO
1    | n-2     | n-2
2    | n-1     | n-1
3    | n       | n+1
4    | n+1     | n+1 

2) Change the CurrSO for items with PriorSO (n - m) to (n – (1 * k)) to value (CurrSO + (1 * k))
Item | PriorSO | CurrSO
1    | n-2     | n-2
2    | n-1     | n-1
3    | n       | n+1
4    | n+1     | n

3) Change the PriorSO for items with CurrSO (n – m) to (n) to value CurrSO
Item | PriorSO | CurrSO
1    | n-2     | n-2
2    | n-1     | n-1
3    | n+1     | n+1
4    | n       | n

If you can understand the algorithm, it is really just a 3 step process. I'll put it out here when I get to testing.

 

[PHV]

A simple ranking query:
SELECT IntWithGap, ...,
(Select Count(*) From theTable Where IntWithGap<=A.IntWithGap) As Rank
FROM theTable AS A
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Axoliien]

Thank you, however we cannot have gaps in our integer details. I am looking for the algorithm to change rank, not so much to actually output the rank of things. Thanks though, I will write when I get a chance to check out actual implementation, we have had something else come up that has taken priority.

 

[willir]

Axoliien

Consider using an Array - load your recordset into the array. (I am assuming you dont have a zillion projects)

Calculate the rank of each project. You can use your algorythm if you wish, or since you have the data loaed, you may find a better way. For example, as ineffecient as it is, you can calculate the MAX missing a RANK assignment.

Update the project table with the calculated rank number.

Richard