0

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
  • If the first integer is truly unlimited, then you can't get enough information about both values into one integer of the same size. Do you mean an index that is only approximately unique? Nov 4, 2013 at 15:38
  • Convert both values to strings, concatenate them with a delimiter, then hash the result? Nov 4, 2013 at 15:40
  • 1
    I think you should describe your problem more, I know how to do what you're asking but I have a feeling your approach is quite suboptimal, if you describe the problem we may well be able to come up with a better approach altogether. Nov 4, 2013 at 15:41
  • 5
    I have a feeling that I'm missing something, and this is not that simple, but why not just X*10+Y?
    – geomagas
    Nov 4, 2013 at 16:02
  • @Dan Hashing may be an idea, but do monotonically increasing hashes exist? I would need to have HashFunction(2,2) > HashFunction(2,1) > HashFunction(1,1). And HasFunction() would need to return an integer. I will make that a separate question, because it is an interesting question it itself.
    – Jan Doggen
    Nov 5, 2013 at 8:07

3 Answers 3

3

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
  • Why did I overlook something so simple ???? It is very unlikely that the user will have so much data that my integer table ID field will overflow, so it still unlikely (but slightly less) that 10 times the ID will overflow. That is a risk I'm willing to take.
    – Jan Doggen
    Nov 5, 2013 at 8:39
  • And kudos to geomagas who wrote this already in a comment, but I did not 'get' that when I read it.
    – Jan Doggen
    Nov 5, 2013 at 8:49
  • @JanDoggen: If X*10 could overflow, then you can perform the same logical operations on the textual representation of the numbers (append Y to X, then prepend zeros to get the right length). Nov 5, 2013 at 9:17
  • 1
    @JanDoggen That's ok, I know I didn't express it the best way possible, but I couldn't post it as an answer either, because I thought I simply didn't understand the question. As for overlooking simple things, well, if God wanted us to see what's under our nose, he wouldn't have given us a nose in the first place ;)
    – geomagas
    Nov 5, 2013 at 15:02
3

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
  • 1
    Such approach as detailed here is most likely suboptimal. The request suggests an approach to solving a problem that doesn't sound so great, I suspect there's an entirely different approach to the problem that is better, but more detail of the actual problem is needed. Nov 4, 2013 at 15:47
  • This is indeed 'Y first' and I need 'X first'
    – Jan Doggen
    Nov 5, 2013 at 7:54
1

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
  • This limits X to a maximum value of 2^^28, which is a variation of the limit to 100 in my original question. Given that my second value only has a range 1..9 I might as well multiply my X by any power of 10 as long as the result does not overflow. So it does not address the fundamental issue of not wanting to set a maximum value for X. But thanks for the effort.
    – Jan Doggen
    Nov 5, 2013 at 7:57
  • I am afraid my requirements are not 'doable', but I thought I'd ask the question anyway because there may be someone somewhere with a smart solution.
    – Jan Doggen
    Nov 5, 2013 at 7:58
  • @JanDoggen - Well, I figured that would be sufficient space to hold any arbitrary user-entered number, but it's true it isn't actually unbounded. I've edited it to discuss the limits. It just boils down to a specific implementation of geomangas and Bart's answers, though.
    – Bobson
    Nov 7, 2013 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.