## (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

General FreeBASIC programming questions.
Posts: 2149
Joined: May 24, 2007 22:10
Location: The Netherlands

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

I have removed the data_type class / type, so it might be easier to follow. Plus some comments.
In addition a added sorting direction for year, day, hour.
And I made the number of columns to sort on variable (0 to 3). With 0 is no sorting.

Code: Select all

`#include "crt/stdlib.bi"type row_type   dim as ushort year_, day_, hour_   dim as string bla1, bla2   declare operator cast () as stringend type'just a function to allow "print row(i)"operator row_type.cast () as string   return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & bla1 & ", " & bla2end operator'enum changed to constantsconst SORT_NONE = 0const SORT_YEAR_UP = 1const SORT_YEAR_DOWN = 2const SORT_DAY_UP = 3const SORT_DAY_DOWN = 4const SORT_HOUR_UP = 5const SORT_HOUR_DOWN = 6dim shared as integer sortOrder(0 to 2) '<-- I don't like this shared / gobal var'pass array as parametersub initRandom(row() as row_type)   for i as integer = 0 to ubound(row)      row(i).year_ = int(rnd * 100) + 1950      row(i).day_ = int(rnd * 365) + 1      row(i).hour_ = int(rnd * 24)      row(i).bla1 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("a"))      row(i).bla2 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("A"))   nextend sub'again array row() passed as parametersub printSome(row() as row_type)   print "--- First 5 items ---"   for i as integer = 0 to 4      print row(i)   next   print "--- Last 5 items ---"   for i as integer = ubound(row) - 4 to ubound(row)      print row(i)   nextend subsub copyAllData(rowSrc() as row_type, rowDst() as row_type)   if ubound(rowSrc) <> ubound(rowDst) then print "Error": exit sub   for i as integer = 0 to ubound(rowSrc)      rowDst(i) = rowSrc(i)   nextend sub'this is called by the sort function to compare to entriesfunction qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as long   for i as integer = 0 to 2      select case sortOrder(i)      case SORT_NONE         'skip      case SORT_YEAR_UP         if pRow1->year_ < pRow2->year_ then return -1         if pRow1->year_ > pRow2->year_ then return +1      case SORT_YEAR_DOWN         if pRow1->year_ > pRow2->year_ then return -1         if pRow1->year_ < pRow2->year_ then return +1      case SORT_DAY_UP         if pRow1->day_ < pRow2->day_ then return -1         if pRow1->day_ > pRow2->day_ then return +1      case SORT_DAY_DOWN         if pRow1->day_ > pRow2->day_ then return -1         if pRow1->day_ < pRow2->day_ then return +1      case SORT_HOUR_UP         if pRow1->hour_ < pRow2->hour_ then return -1         if pRow1->hour_ > pRow2->hour_ then return +1      case SORT_HOUR_DOWN         if pRow1->hour_ > pRow2->hour_ then return -1         if pRow1->hour_ < pRow2->hour_ then return +1      end select   next   return 0end functionsub sort(row() as row_type, sort1st as integer = 0, sort2nd as integer = 0, sort3rd as integer = 0)   print    print sort1st, sort2nd, sort3rd 'print is just for debugging   sortOrder(0) = sort1st   sortOrder(1) = sort2nd   sortOrder(2) = sort3rd   qsort(@row(0), ubound(row) + 1, sizeof(row_type), cptr(any ptr, @qSortCallback))end subconst NUM_ROWS = 1000dim as row_type row(NUM_ROWS - 1)dim as row_type rowBackup(NUM_ROWS - 1)randomize timerinitRandom(row()) 'fill rows with random datacopyAllData(row(), rowBackup()) 'make a backupprint "Unsorted data:"printSome(row())sort(row(), SORT_YEAR_UP, SORT_NONE, SORT_NONE) 'SORT_NONE can be left out, see further examplesprintSome(row())copyAllData(rowBackup(), row()) 'restore backupsort(row(), SORT_YEAR_DOWN, SORT_NONE, SORT_NONE) 'sort in diferent directionprintSome(row())copyAllData(rowBackup(), row())sort(row(), SORT_YEAR_DOWN, SORT_HOUR_DOWN, SORT_NONE) 'add second sort collumnprintSome(row())copyAllData(rowBackup(), row())sort(row(), SORT_NONE, SORT_HOUR_DOWN, SORT_NONE) 'this is weird, same as nextprintSome(row())copyAllData(rowBackup(), row())sort(row(), SORT_HOUR_DOWN) 'more clear right?printSome(row())copyAllData(rowBackup(), row())sort(row()) 'this does nothingprintSome(row())copyAllData(rowBackup(), row())`
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

This looks pretty cool and very close to final result, thank you.

As I dont know/use qsort library function, I have question to sort cycles.
IIUC, qsort/resp. your 'sort()' procedure will sort WHOLE array, right ?
That mean, say, if array has 50 cols and only 3 cols are used/sorted as sorting criteria,
then the rest 50-3=47 cols will be sorted in lost time as balast, right ?
If am right, this immediately leads to improving the code to optimal speed, you know what I mean.
Sort wil be applied only on selected cols as sort criteria, it will produce an index vector,
and via this will be displayed whole array in the best speed time.
Posts: 2149
Joined: May 24, 2007 22:10
Location: The Netherlands

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

ppf wrote:As I dont know/use qsort library function, I have question to sort cycles.

For me, is it also the first time I use qsort (in freebasic).

ppf wrote:IIUC, qsort/resp. your 'sort()' procedure will sort WHOLE array, right ?

Yes, in my example bla1 and bla2 are moved as well.

ppf wrote:That mean, say, if array has 50 cols and only 3 cols are used/sorted as sorting criteria,
then the rest 50-3=47 cols will be sorted in lost time as ballast, right ?

The 'ballast' is just copied around. This could be a performance issue. I don't know how qsort works internally exactly.
Alternatively, we create a list of pointers. Each pointing to 1 row. And sort this list. And then sort the actual data in one loop.
I'll try that later with some benchmarking.

ppf wrote:If am right, this immediately leads to improving the code to optimal speed, you know what I mean.
Sort wil be applied only on selected cols as sort criteria, it will produce an index vector,
and via this will be displayed whole array in the best speed time.

Not sure I follow this .
dodicat
Posts: 6687
Joined: Jan 10, 2006 20:30
Location: Scotland

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

I also like badidea's reasoning.

To sort only part of an array with qsort you say from which lower element to which upper element within the array.
I am sure that badidea can incorporate this feature in his method.
But here is my own method with choices on the fly.
(I keep select case out of the callback by creating dedicated callbacks for any datatypes I use via the macro)
If you compile with -pp option you will see this in the .pp.bas file generated.
Using a type ( or structure/class as some call it) with fixed length arrays (which can ultimately be saved to a file if required ), here is an example:

Code: Select all

` #include "crt/stdlib.bi"'=========  set up c sort =========#define up <,>#define down >,<#define ArrayToSort(x,start,finish) @X((start)),((finish)-(start)+1),Sizeof(X)#macro SetCSort(Datatype,FnName,b1,b2,dot)Function FnName Cdecl(n1 As Any Ptr,n2 As Any Ptr) As Long    If *Cptr(Datatype Ptr,n1)dot b1 *Cptr(Datatype Ptr,n2)dot Then Return -1    If *Cptr(DataType Ptr,n1)dot b2 *Cptr(DataType Ptr,n2)dot Then Return 1    Return 0End Function#endmacroConst columnsize=20Type udt    As Long year(1 To columnsize)    As Long Day(1 To columnsize)    As Long Hour(1 To columnsize)    As String Name(1 To columnsize)    As Single b(1 To columnsize)    As String a(1 To columnsize),c(1 To columnsize),d(1 To columnsize),e(1 To columnsize),f(1 To columnsize)End TypeSub Fill(x As udt,max As Long=columnsize)    Dim As String s(1 To 6)={"Peter","Paul","Mary","Jim","Jean","Anne"}    Randomize 1    For n As Long=1 To max        x.year(n)=2000+Rnd*30        x.day(n)=1+Rnd*7        x.hour(n)=Rnd*24        x.name(n)=s(Int(1+Rnd*6))        x.a(n)=s(Int(1+Rnd*6))        x.b(n)=((Rnd*200))        x.c(n)=s(Int(1+Rnd*6))        x.d(n)=s(Int(1+Rnd*6))        x.e(n)=s(Int(1+Rnd*6))        x.f(n)=s(Int(1+Rnd*6))    NextEnd SubSub printout(x As udt,msg As String="",max As Long=columnsize)    Print Ucase(msg)    Print "year(1)";Tab(10);"day(2)";Tab(20);"hour(3)";Tab(30);"name(4)";Tab(40);"col 5";Tab(50); _    "col 6";Tab(60);"col 7";Tab(70);"col 8";Tab(80);"col 9";Tab(90);"col 10"    Print    For n As Long=1 To max        Print x.year(n);Tab(10);x.day(n);Tab(20);x.hour(n);Tab(30);x.name(n);Tab(40); _        x.a(n);Tab(50);x.b(n);Tab(60);x.c(n);Tab(70);x.d(n);Tab(80);x.e(n);Tab(90);x.f(n)    Next    Print "Press a key . . ."    Print    SleepEnd Sub'set up a sort for each required data type arraySetCsort(Long,longcallback,up,)SetCsort(String,stringcallback,up,)SetCsort(Single,singlecallback,up,)Dim Shared As udt x  'shared --  put on the heap memoryFill(x,columnsize)  'start off with original random values.printout(x,"raw array")Dim As Long c,lower,upperDo        Do         Input "Enter column to sort (1 to 10) ",c        Loop Until c>0 And c<=10        Print "limits available 1 to ";columnsize    Do        Input "enter lower limit ", lower    Loop Until lower>0 And lower <=columnsize        Do        Input "enter upper limit ", upper    Loop Until upper > lower And upper <=columnsize        Print        Select Case c    Case 1        qsort(arraytosort(x.year,Lower,upper),@longcallback)          printout(x,"Sort by year   "+ Str(lower)+ " to "+Str(upper), columnsize)    Case 2        qsort(arraytosort(x.day,Lower,upper),@longcallback)          printout(x,"Sort by day   "+ Str(lower)+ " to "+Str(upper), columnsize)    Case 3        qsort(arraytosort(x.hour,Lower,upper),@longcallback)          printout(x,"Sort by hour   "+ Str(lower)+ " to "+Str(upper), columnsize)    Case 4        qsort(arraytosort(x.name,Lower,upper),@stringcallback)          printout(x,"Sort by name   "+ Str(lower)+ " to "+Str(upper), columnsize)    Case 5        qsort(arraytosort(x.a,Lower,upper),@stringcallback)          printout(x,"Sort by col 5   "+ Str(lower)+ " to "+Str(upper), columnsize)       Case 6        qsort(arraytosort(x.b,Lower,upper),@singlecallback)          printout(x,"Sort by col 6   "+ Str(lower)+ " to "+Str(upper), columnsize)    Case 7         qsort(arraytosort(x.c,Lower,upper),@stringcallback)          printout(x,"Sort by col 7   "+ Str(lower)+ " to "+Str(upper), columnsize)    Case 8        qsort(arraytosort(x.d,Lower,upper),@stringcallback)          printout(x,"Sort by col 8   "+ Str(lower)+ " to "+Str(upper), columnsize)     Case 9        qsort(arraytosort(x.e,Lower,upper),@stringcallback)          printout(x,"Sort by col 9   "+ Str(lower)+ " to "+Str(upper), columnsize)    Case 10        qsort(arraytosort(x.f,Lower,upper),@stringcallback)          printout(x,"Sort by col 10   "+ Str(lower)+ " to "+Str(upper), columnsize)            End Select    Var g=Input(1)    If g=Chr(27) Then EndLoop `
dodicat
Posts: 6687
Joined: Jan 10, 2006 20:30
Location: Scotland

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Example of saving to file:

Code: Select all

`#include "crt/stdlib.bi"#include "file.bi"'=========  set up c sort =========#define up <,>#define down >,<#define ArrayToSort(x,start,finish) @X((start)),((finish)-(start)+1),Sizeof(X)#macro SetCSort(Datatype,FnName,b1,b2,dot)Function FnName Cdecl(n1 As Any Ptr,n2 As Any Ptr) As Long    If *Cptr(Datatype Ptr,n1)dot b1 *Cptr(Datatype Ptr,n2)dot Then Return -1    If *Cptr(DataType Ptr,n1)dot b2 *Cptr(DataType Ptr,n2)dot Then Return 1    Return 0End Function#endmacroConst columnsize=20Type udt    As Long year(1 To columnsize)    As Long Day(1 To columnsize)    As Long Hour(1 To columnsize)    As zString * 100 Name(1 To columnsize)    As Single b(1 To columnsize)    As zstring * 100 a(1 To columnsize),c(1 To columnsize),d(1 To columnsize),e(1 To columnsize),f(1 To columnsize)End Typesub loadfile(file as string,b as udt)   If FileExists(file)=0 Then Print file;" not found":Sleep:end   var  f=freefile    Open file For Binary Access Read As #f    If Lof(f) > 0 Then      Get #f, , b    End If    Close #fend subSub savefile(filename As String,p As udt)    Dim As Integer n    n=Freefile    If Open (filename For Binary Access Write As #n)=0 Then        Put #n,,p        Close    Else        Print "Unable to load " + filename    End IfEnd SubSub Fill(x As udt,max As Long=columnsize)    Dim As String s(1 To 6)={"Peter","Paul","Mary","Jim","Jean","Anne"}    Randomize 1    For n As Long=1 To max        x.year(n)=2000+Rnd*30        x.day(n)=1+Rnd*7        x.hour(n)=Rnd*24        x.name(n)=s(Int(1+Rnd*6))        x.a(n)=s(Int(1+Rnd*6))        x.b(n)=((Rnd*200))        x.c(n)=s(Int(1+Rnd*6))        x.d(n)=s(Int(1+Rnd*6))        x.e(n)=s(Int(1+Rnd*6))        x.f(n)=s(Int(1+Rnd*6))    NextEnd SubSub printout(x As udt,msg As String="",flag as long =0)    Print Ucase(msg)    Print "year(1)";Tab(10);"day(2)";Tab(20);"hour(3)";Tab(30);"name(4)";Tab(40);"col 5";Tab(50); _    "col 6";Tab(60);"col 7";Tab(70);"col 8";Tab(80);"col 9";Tab(90);"col 10"    Print    For n As Long=1 To ubound(x.year)'max        Print x.year(n);Tab(10);x.day(n);Tab(20);x.hour(n);Tab(30);x.name(n);Tab(40); _        x.a(n);Tab(50);x.b(n);Tab(60);x.c(n);Tab(70);x.d(n);Tab(80);x.e(n);Tab(90);x.f(n)    Next   if flag =0 then Print "Press a key or <esc> . . ." else Print "Press a key . . ."    Print    SleepEnd Sub'set up a sort for each required data type arraySetCsort(Long,longcallback,up,)'SetCsort(String,stringcallback,up,)SetCsort(Single,singlecallback,up,)SetCsort(zstring,zstringcallback,up,)Dim Shared As udt x  'shared --  put on the heap memoryFill(x,columnsize)  'start off with original random values.printout(x,"raw array",1)Dim As Long c,lower,upperDo        Do         Input "Enter column to sort (1 to 10) ",c        Loop Until c>0 And c<=10        Print "limits available 1 to ";columnsize    Do        Input "enter lower limit ", lower    Loop Until lower>0 And lower <=columnsize        Do        Input "enter upper limit ", upper    Loop Until upper > lower And upper <=columnsize        Print        Select Case c    Case 1        qsort(arraytosort(x.year,Lower,upper),@longcallback)          printout(x,"Sort by year   "+ Str(lower)+ " to "+Str(upper))    Case 2        qsort(arraytosort(x.day,Lower,upper),@longcallback)          printout(x,"Sort by day   "+ Str(lower)+ " to "+Str(upper))    Case 3        qsort(arraytosort(x.hour,Lower,upper),@longcallback)          printout(x,"Sort by hour   "+ Str(lower)+ " to "+Str(upper))    Case 4        qsort(arraytosort(x.name,Lower,upper),@zstringcallback)          printout(x,"Sort by name   "+ Str(lower)+ " to "+Str(upper))    Case 5        qsort(arraytosort(x.a,Lower,upper),@zstringcallback)          printout(x,"Sort by col 5   "+ Str(lower)+ " to "+Str(upper))       Case 6        qsort(arraytosort(x.b,Lower,upper),@singlecallback)          printout(x,"Sort by col 6   "+ Str(lower)+ " to "+Str(upper))    Case 7         qsort(arraytosort(x.c,Lower,upper),@zstringcallback)          printout(x,"Sort by col 7   "+ Str(lower)+ " to "+Str(upper))    Case 8        qsort(arraytosort(x.d,Lower,upper),@zstringcallback)          printout(x,"Sort by col 8   "+ Str(lower)+ " to "+Str(upper))     Case 9        qsort(arraytosort(x.e,Lower,upper),@zstringcallback)          printout(x,"Sort by col 9   "+ Str(lower)+ " to "+Str(upper))    Case 10        qsort(arraytosort(x.f,Lower,upper),@zstringcallback)          printout(x,"Sort by col 10   "+ Str(lower)+ " to "+Str(upper))            End Select    Var g=Input(1)    If g=Chr(27) Then exit doLoopprint "------ now using file ------"savefile("columns.dat",x)dim as udt yloadfile("columns.dat",y)printout(y,"From saved file columns.dat",1)'kill "columns.dat"  `
Posts: 2149
Joined: May 24, 2007 22:10
Location: The Netherlands

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

I switched back to my 'class' version, with some changes.
One can define the number of additional columns with const NUM_OTHER_COLS = 1
The added columns are all (dynamic) strings. I ran a test with 1 and with 50 other columns.
The difference in sorting time seems not worth the trouble 'sorting pointers first'.

NUM_OTHER_COLS = 1
fbc32 v1.07.0 -exx --> ~0.62 sec
fbc64 v1.07.0 --> ~0.30 sec

NUM_OTHER_COLS = 50
fbc32 v1.07.0 -exx --> ~0.89 sec
fbc64 v1.07.0 --> 0.55 sec

Test code:

Code: Select all

`#include "crt/stdlib.bi"#include "string.bi"const NUM_OTHER_COLS = 1type row_type   dim as ushort year_, day_, hour_   dim as string otherColumns(NUM_OTHER_COLS - 1)   declare operator cast () as stringend typeoperator row_type.cast () as string   return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & ubound(otherColumns) + 1end operatorenum E_SORT_DATA   SORT_NONE = 0   SORT_YEAR_UP = 1   SORT_YEAR_DOWN = 2   SORT_DAY_UP = 3   SORT_DAY_DOWN = 4   SORT_HOUR_UP = 5   SORT_HOUR_DOWN = 6end enumtype data_type   static as E_SORT_DATA sortOrder(0 to 2)   dim as row_type row(any)   declare constructor(numRows as integer)   declare destructor()   declare sub initRandom()   declare sub printSome()   declare sub copyTo(dst as data_type)   declare sub sort(sort1st as E_SORT_DATA = 0, sort2nd as E_SORT_DATA = 0, sort3rd as E_SORT_DATA = 0)   declare static function qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as longend typedim as E_SORT_DATA data_type.sortOrder(0 to 2)constructor data_type(numRows as integer)   redim row(numRows - 1)end constructordestructor data_type()   print hex(@this)   erase rowend destructorsub data_type.initRandom()   for iRow as integer = 0 to ubound(row)      with row(iRow)         .year_ = int(rnd * 100) + 1950         .day_ = int(rnd * 365) + 1         .hour_ = int(rnd * 24)         for iCol as integer = 0 to ubound(.otherColumns)            .otherColumns(iCol) = string(int(rnd * 10) + 1, int(rnd * 26) + asc("a"))         next      end with   nextend subsub data_type.printSome()   print "--- First 5 items ---"   for i as integer = 0 to 4      print row(i)   next   print "--- Last 5 items ---"   for i as integer = ubound(row) - 4 to ubound(row)      print row(i)   nextend subsub data_type.copyTo(dst as data_type)   if ubound(row) <> ubound(dst.row) then print "Error": exit sub   for i as integer = 0 to ubound(row)      dst.row(i) = row(i)   nextend subsub data_type.sort(sort1st as E_SORT_DATA = 0, sort2nd as E_SORT_DATA = 0, sort3rd as E_SORT_DATA = 0)   sortOrder(0) = sort1st   sortOrder(1) = sort2nd   sortOrder(2) = sort3rd   qsort(@row(0), ubound(row) + 1, sizeof(row_type), cptr(any ptr, @qSortCallback))end subfunction data_type.qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as long   for i as integer = 0 to 2      select case sortOrder(i)      case SORT_NONE         'skip      case SORT_YEAR_UP         if pRow1->year_ < pRow2->year_ then return -1         if pRow1->year_ > pRow2->year_ then return +1      case SORT_YEAR_DOWN         if pRow1->year_ > pRow2->year_ then return -1         if pRow1->year_ < pRow2->year_ then return +1      case SORT_DAY_UP         if pRow1->day_ < pRow2->day_ then return -1         if pRow1->day_ > pRow2->day_ then return +1      case SORT_DAY_DOWN         if pRow1->day_ > pRow2->day_ then return -1         if pRow1->day_ < pRow2->day_ then return +1      case SORT_HOUR_UP         if pRow1->hour_ < pRow2->hour_ then return -1         if pRow1->hour_ > pRow2->hour_ then return +1      case SORT_HOUR_DOWN         if pRow1->hour_ > pRow2->hour_ then return -1         if pRow1->hour_ < pRow2->hour_ then return +1      end select   next   return 0end functiondim as double tStart, tEndrandomize timerprint "Allocate memory"dim as data_type myData = data_type(500000), backupData = data_type(500000)print "Initialize with random data"myData.initRandom()print "Make backup"myData.copyTo(backupData)print "Unsorted data:"myData.printSome()print !"\nmyData.sort(YEAR_UP, DAY_UP, HOUR_UP):"tStart = timermyData.sort(SORT_YEAR_UP, SORT_DAY_UP, SORT_HOUR_UP)tEnd = timerprint "Sort duration [s]: " & format(tEnd - tStart, "0.000")myData.printSome()backupData.copyTo(myData)print !"\nmyData.sort(HOUR_DOWN, DAY_DOWN, YEAR_DOWN):"tStart = timermyData.sort(SORT_YEAR_DOWN, SORT_DAY_DOWN, SORT_HOUR_DOWN)tEnd = timerprint "Sort duration [s]: " & format(tEnd - tStart, "0.000")myData.printSome()print !"\nPress any key to end"sleepprint "End"`

Note: with fbc 64-bit the code allocates ~3.4 GB !
Change 500000 (twice) to a lower value for less memory usage

I hope I did not make a mistake, because sorting a few GB within a second seems almost impossible.
The actual data sorted is less; half of it is the backup and only the string descriptors are probably moved around.
fxm
Posts: 9939
Joined: Apr 22, 2009 12:46
Location: Paris suburbs, FRANCE

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

The Type structure contains only an array of string descriptors, and qsort only works on these to sort the list (string character data is dynamically allocated elsewhere in memory).
Only your callback function access in read to the string character data when comparing the elements pointed by qsort.
Therefore qsort only swap the descriptors depending on your callback function returns.
jj2007
Posts: 1692
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

badidea wrote:I switched back to my 'class' version, with some changes.
Works like a charm!

In case somebody wants to test stable string sorts, here is a tab-delimited demo file with 1000 lines. Example, sorted first by City (Belyando, ...), then by First name (Aaron, ...):

Code: Select all

`Aaliyah   2716 Simpson Street   BelyandoAaron   2033 Rosales Street   Port IattaAbagail   1381 Lee Street   Palmers OakyAbagail   2445 Price Street   PopanyinningAbagail   35 Boyer Street   Red RangeAbagail   957 Mueller Street   Southern CrossAbbigail   2633 Jordan Street   Carlisle RiverAbbigail   2568 Mejia Street   KentAbbigail   2086 Newton Street   Mount Marlow`
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Changing control loop logic in my app, as I don't have smart routine to catch +manage keypresses to generate Multi-column sort flag.As I prefer on-the-fly speedy visual control, using Gui dialogs for column selection is not suitable for speed. Just need to solve only typo eventually happened and rebuil/repair word of sort flag.
Except this, seems the post title can be marked as solved.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Could you wrote a version of your solution for this type, please ?
[code]
type row_type
rem original ' dim as ushort year_,day_,hour_
dim as ushort pivotMS(any)
dim as string bla1,bla2
declare operator cast () as string
end type

Function ''qsortCallback'' modified with internal loop for variable pivotMS() will be smart and elegant solution.

Thank you
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Hmm, this is weird.Help, please ..
Slowly finishing to universal prototypes of Multi-cols sort method interface, but problem appeared.

What is right syntax for dimensioning this datatype to (1 to su5) elements ?

Code: Select all

`type row_typeMS   dim as integer pivotMS(any)   dim as integer indexMSend type`

Couple of errors appeared at dimming.

But when it's dimmed to (0 to su5), equal notation (su5) elements, compiled Ok.
Working code

Code: Select all

`redim row(su5) as row_typeMS   redim (row(su5).pivotMS) (sortOrderMax+1)`

And finally, what about dimmnig to (-7 to 44) elements, is it possible ?

Thanks for any advices.
fxm
Posts: 9939
Joined: Apr 22, 2009 12:46
Location: Paris suburbs, FRANCE

### Re: (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Because the array member ('pivotMS()') of each 'row_typeMS' instance must be sized explicitly and not the range ('1 to su5') in one go nor only the last (the 'su5'th):

Code: Select all

`type row_typeMS   dim as integer pivotMS(any)   dim as integer indexMSend typeredim row(1 to su5) as row_typeMSfor i as integer = 1 to su5  ' or: for i as integer = lbound(row) to ubound(row)   redim (row(i).pivotMS)(sortOrderMax+1)next i`
or

Code: Select all

`type row_typeMS   dim as integer pivotMS(any)   dim as integer indexMSend typeredim row(-7 to 44) as row_typeMSfor i as integer = -7 to 44  ' or: for i as integer = lbound(row) to ubound(row)   redim (row(i).pivotMS)(sortOrderMax+1)next i`
Posts: 2149
Joined: May 24, 2007 22:10
Location: The Netherlands

### Re: (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Could you wrote a version of your solution for this type, please ?

Working on it now. The 'select case' part needs to be replaced with something else for this.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Wau, it works! I see my small datasheet under multisort!
You are simply genious, fxm; BIG thanks !!
Posts: 2149
Joined: May 24, 2007 22:10
Location: The Netherlands

### Re: (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

My modified version (col = pivotMS):

Code: Select all

`#include "crt/stdlib.bi"#include "string.bi"'------------------------------- class: row_type -------------------------------type row_type   dim as ushort col(any)   dim as string bla1, bla2 'some extra (passive) columns   declare operator cast () as stringend typeoperator row_type.cast () as string   dim as string tempStr   for i as integer = 0 to ubound(col)      if i = 0 then tempStr &= col(i) else tempStr &= !"\t" & col(i)    next   return tempStr & !"\t" & bla1  & !"\t" & bla2 end operator'------------------------------ class: sort_type -------------------------------type sort_type   dim as short column   dim as short direction   declare constructor()   declare constructor(column as short, direction as short)end type'a stupid constructorconstructor sort_type()   this.column = 0   this.direction = 0end constructor'another stupid constructorconstructor sort_type(column as short, direction as short)   this.column = column   this.direction = directionend constructor'------------------------------ class: data_type -------------------------------type data_type   static as sort_type sortOrder(0 to 2)   static as integer numRows, numCols   dim as row_type row(any)   declare constructor(numRows as integer, numPivotMS as integer)   declare destructor()   declare sub initRandom()   declare sub printSome()   declare sub copyTo(dst as data_type)   declare sub sort(sort1st as sort_type, sort2nd as sort_type, sort3rd as sort_type)   declare static function qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as longend typedim as sort_type data_type.sortOrder(0 to 2)dim as integer data_type.numCols = 0dim as integer data_type.numRows = 0constructor data_type(numRows as integer, numCols as integer)   redim row(numRows - 1)   this.numCols = numCols   for iRow as integer = 0 to numRows - 1      redim (row(iRow).col)(numCols - 1) 'weird syntax, compiler wants the extra ( )   nextend constructordestructor data_type()   for iRow as integer = 0 to numRows - 1      erase row(iRow).col   next   erase rowend destructorsub data_type.initRandom()   for iRow as integer = 0 to ubound(row)      with row(iRow)         for iCol as integer = 0 to ubound(.col)            .col(iCol) = int(rnd * 1000)          next         .bla1 = string(int(rnd * 7) + 1, int(rnd * 26) + asc("a"))         .bla2 = string(int(rnd * 7) + 1, int(rnd * 26) + asc("A"))      end with   nextend subsub data_type.printSome()   print "--- First 5 items ---"   for i as integer = 0 to 4      print row(i)   next   print "--- Last 5 items ---"   for i as integer = ubound(row) - 4 to ubound(row)      print row(i)   nextend subsub data_type.copyTo(dst as data_type)   if ubound(row) <> ubound(dst.row) then print "Error": exit sub   for i as integer = 0 to ubound(row)      dst.row(i) = row(i)   nextend subsub data_type.sort(sort1st as sort_type, sort2nd as sort_type, sort3rd as sort_type)   sortOrder(0) = sort1st   sortOrder(1) = sort2nd   sortOrder(2) = sort3rd   qsort(@row(0), ubound(row) + 1, sizeof(row_type), cptr(any ptr, @qSortCallback))end subfunction data_type.qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as long   for i as integer = 0 to 2      with sortOrder(i)         if .column < 0 then continue for 'out of bounds, skip         if .column >= numCols then continue for 'out of bounds, skip         select case .direction         case +1            if pRow1->col(.column) < pRow2->col(.column) then return -1            if pRow1->col(.column) > pRow2->col(.column) then return +1         case -1            if pRow1->col(.column) > pRow2->col(.column) then return -1            if pRow1->col(.column) < pRow2->col(.column) then return +1         case else            'skip, including direction = 0         end select      end with   next   return 0end function'-------------------------------- main program ---------------------------------dim as integer numRows = 2000, numCols = 5randomize timerprint "Allocate memory"var myData = data_type(numRows, numCols), backupData = data_type(numRows, numCols)print "Initialize with random data"myData.initRandom()print "Make backup"myData.copyTo(backupData)print "Unsorted data:"myData.printSome()print !"\nSort: col 0 up, col 2 down:"myData.sort(sort_type(0, +1), sort_type(2, -1), sort_type(0, 0))myData.printSome()backupData.copyTo(myData)print !"\nSort: col 1 up (only)"myData.sort(sort_type(0, +1), sort_type(0, 0), sort_type(0, 0))myData.printSome()print !"\nPress any key to end"sleepprint "End"`

Only shortly tested. Enough of this crazy stuff. Back to my own simple project...

Edit: The 'out-of-bounds' checking in qSortCallback() can be moved to sort(). Will be faster that way. See next post.
Last edited by badidea on Dec 15, 2019 21:54, edited 1 time in total.