Sort an array or structure by two columns?

[SamIWas]

Using SortArray is easy when sorting an array by a single column.  I can't find a method for sorting by two columns.  

 

For instance, I have an array with X and Y values.  I want the array sorted by X, and then Y.  I tries sorting Y first, then X, hoping that it would hold the Y sort, but it does not.  I've considered making a third column which is just "sort order" and some method of the two values combined to make a sortable list.  Is there a better method?

[Pat Stanford]

Not what I am seeing. Running sort twice on an array keeps the first sort order and than also applies the second sort.

 

The script in the attached file when run on a blank file with a layer scale of 1/4" (1:48) will display the original array, and then a first sort by the Text field and then a second sort on the number field.  If you want to use a different scale, then you will probably want to change the settings in the calls to the TxtBlock function to get the correct X spacing of the text columns I am using to output the arrays for visual comparison.

 

Note that the Sort on the numbers is a true numeric and that 2 sorts to before 10. Is it possible that you are using a numeric field and expecting an ascii sort where 19 sorts before 2 then 20, 21, etc?

 

Or maybe you need to so your sorts in the opposite order.  Sort by the least important first then by the more important.

 

I attached the file because the script uses CHR functions that cause an error in the forum code block interpreter.

 

 

 

SortArray Order Text.vwx

[SamIWas]

3 minutes ago, Pat Stanford said:

Not what I am seeing. Running sort twice on an array keeps the first sort order and than also applies the second sort.

 

The script in the attached file when run on a blank file with a layer scale of 1/4" (1:48) will display the original array, and then a first sort by the Text field and then a second sort on the number field.  If you want to use a different scale, then you will probably want to change the settings in the calls to the TxtBlock function to get the correct X spacing of the text columns I am using to output the arrays for visual comparison.

 

Note that the Sort on the numbers is a true numeric and that 2 sorts to before 10. Is it possible that you are using a numeric field and expecting an ascii sort where 19 sorts before 2 then 20, 21, etc?

 

Or maybe you need to so your sorts in the opposite order.  Sort by the least important first then by the more important.

 

I attached the file because the script uses CHR functions that cause an error in the forum code block interpreter.

 

 

So, in the image below are the coordinates of some objects in my file as reported by the script and entered into the array.  The coordinates were entered randomly as it picked up objects.  When I sort by X (column 1), I will get all the X column in order, as expected.  Note that several X values are the same, but Y values are different.  If I want it sorted by X, and then Y,  I hoped that I could sort by column 2, and then column 1.  But, that doesn't work.  

 

In the meantime, I might divide the Y value by ten million or something and add that to the X value, giving a point value sort in a single column.

 

[Sam Jones]

One assumes that you want to sort by a prime column, and then you want to sort by the second column within all the identical prime values.

 

AA    1

AA    2

AA    3

AB    1

AB    2

AB    3

AB    4

AB    5

BB    1

BB    2

BB    3

BB    4

 

Or for what you describe:

 

X, Y

8, 5

9, 5

10, 5

4, 11

5, 11

6, 11

7, 11

1, 23

2, 23

3, 23

 

 

You would have to use your own sort algorithm and put it into a procedure or code block.  Making a reusable procedure is tricky but a code block is simple.  The simplest that I know of would be a bubble sort. 

 

A bubble sort on 2 values is most easily coded using a STRUCTURE type in VS, but isn't required.  Bubble sorts are not efficient, but they work well hundreds of elements even the low thousands, more than that and you would want something more efficient.

 

Let me know if you need a code sample of a bubble sort.

 

 

 

[Pat Stanford]

My best guess is that you are seeing rounding errors.

 

349.813000001 vs 349.813.000002

Floating point numbers are not exact.

 

I would suggest that you clean your data before you store it in the array to the number of decimal points you want.  Something like:

 

Array[N].X = Trunc(X_Value*1000)/1000

 

should get you a value that only contains zeros beyond 3 decimal points.  The X_Value *1000 shifts the decimal point three places to the right. The Trunc give you a long integer of that shifted value. The /1000 shifts the decimal place back to the original value.

 

10 will give you one decimal place. 100 will give two. etc.

 

HTH

[SamIWas]

3 hours ago, Sam Jones said:

One assumes that you want to sort by a prime column, and then you want to sort by the second column within all the identical prime values.

 

AA    1

AA    2

AA    3

AB    1

AB    2

AB    3

AB    4

AB    5

BB    1

BB    2

BB    3

BB    4

 

Or for what you describe:

 

X, Y

8, 5

9, 5

10, 5

4, 11

5, 11

6, 11

7, 11

1, 23

2, 23

3, 23

 

 

You would have to use your own sort algorithm and put it into a procedure or code block.  Making a reusable procedure is tricky but a code block is simple.  The simplest that I know of would be a bubble sort. 

 

A bubble sort on 2 values is most easily coded using a STRUCTURE type in VS, but isn't required.  Bubble sorts are not efficient, but they work well hundreds of elements even the low thousands, more than that and you would want something more efficient.

 

Let me know if you need a code sample of a bubble sort.

 

 

 

 

I thought about doing a bubble sort, but as you said...it's quite inefficient.  

 

3 hours ago, Pat Stanford said:

My best guess is that you are seeing rounding errors.

 

349.813000001 vs 349.813.000002

Floating point numbers are not exact.

 

I would suggest that you clean your data before you store it in the array to the number of decimal points you want.  Something like:

 

Array[N].X = Trunc(X_Value*1000)/1000

 

should get you a value that only contains zeros beyond 3 decimal points.  The X_Value *1000 shifts the decimal point three places to the right. The Trunc give you a long integer of that shifted value. The /1000 shifts the decimal place back to the original value.

 

10 will give you one decimal place. 100 will give two. etc.

 

HTH

 

Actually, doing the trunc(x/1000)*1000 is exactly how the value is entered into the array.  I do that frequently for value calculations and display.

 

 

What I have ended up doing is adding a third column to the structure.  one value is entered to three decimal points, then the other value is added, but divided by ten million.  So an X value of 375.268 and a Y value of 198.376 goes into the sort column as 375.268000198376.  If I sort by Y, it reverses the two.   This seems to work and is much more efficient than a bubble sort.