I have table data containing an integer value X ranging from 1…. unknown, and an integer value Y ranging from 1..9
The data need to be presented in order ‘X then Y’.
For one visual component I can set multiple index names: X;Y
But for another component I need a one-dimensional integer value as index (sort order).
If X were limited to an upper bound of say 100, the one-dimensional value could simply be X*100 + Y.
If the one-dimensional value could have been a real, it could be X + Y/10.
But if I want to keep X unlimited, is there a way to calculate a single integer ‘indexing’ value from X and Y?
[Added] Background information:
I have a Gantt/TreeList component where the tasks are ordered on a TaskIndex integer. This does not need to be a real database field, I can make it a calculated field in the underlying client dataset.
My table data is e.g. as follows:
ID Baseline ParentID
1 0 0 (task)
5 2 1 (baseline)
8 1 1 (baseline)
9 0 0 (task)
12 0 0 (task)
16 1 12 (baseline)
Task 1 has two baselines numbered 1 and 2 (IDs 8 and 5)
Task 9 has no baselines
Task 12 has one baseline numbered 1 (ID 16)
Baselines number 1-9 (the Y variable from my question); 0 or null identify the tasks
ID’s are unlimited (the X variable)
The user plays with visibility of baselines, e.g. he wants to see all tasks and all baselines labeled 1. This is done by updating a filter on the table.
Right now I constantly have to recalculate TaskIndex after changing the filter (looping through records with a counter). It would be nice if TaskIndex could be calculated on the fly for each record knowing only the ID and Baseline data in the current record (I work in Delphi where a client dataset has an OnCalcFields event handler, that is triggered for each record when necessary).
I have no control over the inner workings of the visual component.
5
As your least-significant index (Y) is bounded, to get the right sort order, you can just do index = X*10 + Y and order index on its numerical value.
If the overall index is too large to fit into an integer value, you can simulate numerical ordering by padding the shorter numbers with leading zeros until all values in the comparison are the same length.
4
since Y is bounded you can just use that as prefix because numbers grow to the right, so you can’t use it as a suffix.
So if Y is 8 and X is a 4000 digit number, it would be 8XXXXXXXXXXXXXXXXXXXXXX[…].
Though I read that you are trying to do sorting, so this form of sorting would be Y first, X second which may not be what you want.
If you want to sort using the composition of X and Y with X as the primary, You may just want to have something sorted by X, where each element is another collection sorted by Y, then you can use a simplistic iteration over the set to get a flat list that is sorted as you wish.
2
I’ve run into a similar problem before, and I’ve solved it by taking advantage of the fact that padded binary will always sort correctly, whether parsed as a string or a number.
Here’s pseudocode (padding to make a 32-digit number, you can trim it down):
define X,Y as int
define Xbin, Ybin as string
Xbin = ConvertToBinary(X).PadLeft(28, "0")
Ybin = ConvertToBinary(Y).PadLeft(4, "0")
return Xbin + Ybin
For 5 randomly chosen sets of data:
6737, 7 -> 00000000000000011010010100010111
51, 6 -> 00000000000000000000001100110110
2, 0 -> 00000000000000000000000000100000
864, 5 -> 00000000000000000011011000000101
165, 3 -> 00000000000000000000101001010011
Sorted (whether by string value or numeric value),
00000000000000000000000000100000 <- 2, 0
00000000000000000000001100110110 <- 51, 6
00000000000000000000101001010011 <- 165, 3
00000000000000000011011000000101 <- 864, 5
00000000000000011010010100010111 <- 6737, 7
*Edit: * I used 228 worth of storage here, but since these can be stored as strings, there’s no effective upper limit. If you’re storing the X in the database as an int, then you’d need to use 32 as the X-padding number. If long, 64. If you’re using something which supports larger-than-64bit numbers, just keep expanding as appropriate.
If you’re storing it as a string/varchar in the database, you have a problem. If your language/database has a maximum length for a string, then you can’t guarantee you won’t overflow by adding extra information into it. If you don’t have a limit, you can’t calculate the maximum padding you’d need.
3