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

General FreeBASIC programming questions.
badidea
Posts: 1620
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) ?

Postby badidea » Dec 02, 2019 22:36

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 string
end type

'just a function to allow "print row(i)"
operator row_type.cast () as string
   return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & bla1 & ", " & bla2
end operator

'enum changed to constants
const SORT_NONE = 0
const SORT_YEAR_UP = 1
const SORT_YEAR_DOWN = 2
const SORT_DAY_UP = 3
const SORT_DAY_DOWN = 4
const SORT_HOUR_UP = 5
const SORT_HOUR_DOWN = 6

dim shared as integer sortOrder(0 to 2) '<-- I don't like this shared / gobal var

'pass array as parameter
sub 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"))
   next
end sub

'again array row() passed as parameter
sub 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)
   next
end sub

sub 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)
   next
end sub

'this is called by the sort function to compare to entries
function 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 0
end function

sub 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 sub

const NUM_ROWS = 1000
dim as row_type row(NUM_ROWS - 1)
dim as row_type rowBackup(NUM_ROWS - 1)

randomize timer
initRandom(row()) 'fill rows with random data
copyAllData(row(), rowBackup()) 'make a backup

print "Unsorted data:"
printSome(row())

sort(row(), SORT_YEAR_UP, SORT_NONE, SORT_NONE) 'SORT_NONE can be left out, see further examples
printSome(row())
copyAllData(rowBackup(), row()) 'restore backup

sort(row(), SORT_YEAR_DOWN, SORT_NONE, SORT_NONE) 'sort in diferent direction
printSome(row())
copyAllData(rowBackup(), row())

sort(row(), SORT_YEAR_DOWN, SORT_HOUR_DOWN, SORT_NONE) 'add second sort collumn
printSome(row())
copyAllData(rowBackup(), row())

sort(row(), SORT_NONE, SORT_HOUR_DOWN, SORT_NONE) 'this is weird, same as next
printSome(row())
copyAllData(rowBackup(), row())

sort(row(), SORT_HOUR_DOWN) 'more clear right?
printSome(row())
copyAllData(rowBackup(), row())

sort(row()) 'this does nothing
printSome(row())
copyAllData(rowBackup(), row())
ppf
Posts: 58
Joined: Oct 10, 2017 6:41

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

Postby ppf » Dec 03, 2019 2:54

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.
badidea
Posts: 1620
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) ?

Postby badidea » Dec 03, 2019 9:40

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: 6030
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) ?

Postby dodicat » Dec 03, 2019 10:03

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 0
End Function
#endmacro

Const columnsize=20

Type 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 Type

Sub 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))
    Next
End Sub

Sub 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
    Sleep
End Sub

'set up a sort for each required data type array
SetCsort(Long,longcallback,up,)
SetCsort(String,stringcallback,up,)
SetCsort(Single,singlecallback,up,)

Dim Shared As udt x  'shared --  put on the heap memory
Fill(x,columnsize)  'start off with original random values.
printout(x,"raw array")

Dim As Long c,lower,upper

Do
   
    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 End
Loop


 
dodicat
Posts: 6030
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) ?

Postby dodicat » Dec 03, 2019 12:27

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 0
End Function
#endmacro

Const columnsize=20

Type 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 Type

sub 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 #f
end sub

Sub 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 If
End Sub

Sub 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))
    Next
End Sub

Sub 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
    Sleep
End Sub

'set up a sort for each required data type array
SetCsort(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 memory
Fill(x,columnsize)  'start off with original random values.
printout(x,"raw array",1)

Dim As Long c,lower,upper

Do
   
    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 do
Loop

print "------ now using file ------"

savefile("columns.dat",x)

dim as udt y
loadfile("columns.dat",y)
printout(y,"From saved file columns.dat",1)

'kill "columns.dat"

 
badidea
Posts: 1620
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) ?

Postby badidea » Dec 03, 2019 23:23

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 = 1

type row_type
   dim as ushort year_, day_, hour_
   dim as string otherColumns(NUM_OTHER_COLS - 1)
   declare operator cast () as string
end type

operator row_type.cast () as string
   return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & ubound(otherColumns) + 1
end operator

enum 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 = 6
end enum

type 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 long
end type
dim as E_SORT_DATA data_type.sortOrder(0 to 2)

constructor data_type(numRows as integer)
   redim row(numRows - 1)
end constructor

destructor data_type()
   print hex(@this)
   erase row
end destructor

sub 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
   next
end sub

sub 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)
   next
end sub

sub 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)
   next
end sub


sub 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 sub

function 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 0
end function

dim as double tStart, tEnd
randomize timer
print "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 = timer
myData.sort(SORT_YEAR_UP, SORT_DAY_UP, SORT_HOUR_UP)
tEnd = timer
print "Sort duration [s]: " & format(tEnd - tStart, "0.000")
myData.printSome()

backupData.copyTo(myData)

print !"\nmyData.sort(HOUR_DOWN, DAY_DOWN, YEAR_DOWN):"
tStart = timer
myData.sort(SORT_YEAR_DOWN, SORT_DAY_DOWN, SORT_HOUR_DOWN)
tEnd = timer
print "Sort duration [s]: " & format(tEnd - tStart, "0.000")
myData.printSome()

print !"\nPress any key to end"
sleep
print "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: 9316
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) ?

Postby fxm » Dec 04, 2019 5:59

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: 1263
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) ?

Postby jj2007 » Dec 06, 2019 11:22

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   Belyando
Aaron   2033 Rosales Street   Port Iatta
Abagail   1381 Lee Street   Palmers Oaky
Abagail   2445 Price Street   Popanyinning
Abagail   35 Boyer Street   Red Range
Abagail   957 Mueller Street   Southern Cross
Abbigail   2633 Jordan Street   Carlisle River
Abbigail   2568 Mejia Street   Kent
Abbigail   2086 Newton Street   Mount Marlow
ppf
Posts: 58
Joined: Oct 10, 2017 6:41

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

Postby ppf » Dec 07, 2019 13:10

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: 58
Joined: Oct 10, 2017 6:41

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

Postby ppf » Dec 12, 2019 15:53

@ badidea
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: 58
Joined: Oct 10, 2017 6:41

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

Postby ppf » Dec 15, 2019 5:07

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 indexMS
end 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: 9316
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) ?

Postby fxm » Dec 15, 2019 8:14

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 indexMS
end type

redim row(1 to su5) as row_typeMS
for 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 indexMS
end type

redim row(-7 to 44) as row_typeMS
for i as integer = -7 to 44  ' or: for i as integer = lbound(row) to ubound(row)
   redim (row(i).pivotMS)(sortOrderMax+1)
next i
badidea
Posts: 1620
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) ?

Postby badidea » Dec 15, 2019 20:04

ppf wrote:@ badidea
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.

Return to “General”

Who is online

Users browsing this forum: No registered users and 2 guests