multiple sort [SOLVED]

[gcue]

hello.

i am trying to sort a multi-column array. here is an example array:

local $array[3][3]

$array[0][0] = "D002222"
$array[0][1] = "002129"
$array[0][2] = "948"

$array[1][0] = "D003333"
$array[1][1] = "002127"
$array[1][2] = "200"

$array[2][0] = "D001111"
$array[2][1] = "002124"
$array[2][2] = "6789"

i would like to sort first by column 1 then by column 2 then by column 3

thank you for your help in advance =)

Edited January 5, 2011 by gcue

[Melba23]

gcue,

Two comments:

- 1. What have you tried so far?

- 2. You need to be much more specific as to the result you want. "sort first by column 1 then by column 2 then by column 3" can mean many things - my impression of what you are looking for is this:

A   A   A
A   A   B
A   B   A
A   B   B
B   A   A
B   A   B
B   B   A
B   B   B

Is that also what you envisage?

M23

[iamtheky]

as well, you will have to enter numbers without quotes, otherwise it will treat them as strings. Run the following and it sorts them in one after the other. throw the 3rd element back into quotes and you will see 6789 stay in the middle of the last array.

#include <array.au3>

local $array[3][3]

$array[0][0] = "D002222"
$array[0][1] = "002129"
$array[0][2] = 948

$array[1][0] = "D003333"
$array[1][1] = "002127"
$array[1][2] = 200

$array[2][0] = "D001111"
$array[2][1] = "002124"
$array[2][2] = 6789



_ArraySort ($array , 0 , 0 , 0 , 0)

_ArrayDisplay ($array)

_ArraySort ($array , 0 , 0 , 0 , 1)

_ArrayDisplay ($array)

_ArraySort ($array , 0 , 0 , 0 , 2)

_ArrayDisplay ($array)

Edited January 5, 2011 by iamtheky

[UEZ]

Try this:

 

expandcollapse popup

#include <array.au3>
$max = 50
$s = ""
Dim $array[$max][4]
for $i = 0 to $max - 1
    $array[$i][0] = Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1))
    For $j = 0 To 2
        If Random(0, 1, 1) Then $s &= Chr(Random(48, 57, 1))
    Next
    If $s = "" Then $s = Chr(Random(65, 67, 1))
    $array[$i][1] =  $s
    $array[$i][2] = Random(1, 10, 1)
    $m = Random(1, 12, 1)
    $d = Random(1, 31, 1)
    If $m < 10 Then $m = "0" & $m
    If $d < 10 Then $d = "0" & $d
    $array[$i][3] = Random(1900, 2010, 1) &  "-" & $m & "-" & $d
    $s = ""
Next
_ArrayDisplay($array, 'Before:')
Dim $aI[3]
;order of sorting rows - here row 0,3,2
$aI[0] = 0
$aI[1] = 3
$aI[2] = 2

_ArraySort_MultiColumn($array, $aI)
_ArrayDisplay($array, 'After:')



; #FUNCTION# =============================================================================
; Name.............:    _ArraySort_MultiColumn
; Description ...:      sorts a 2D array at given columns (multi column sort)
; Syntax...........:    _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices)
; Parameters ...:       $aSort - array to sort
;                       $aIndices - array with column indices which should be sorted in specified order - zero based
;                       $oDir/$iDir - sort direction - if set to 1, sort descending else ascending. $oDir is for the 1st column,
;                                                     $iDir for the remaining columns
;                       $iStart - where to start to sort - 0 = 0 based array, 1 = 1 based array
; Return values.:       Success: 1
;                       Failure: 0 and sets @error:
;                       1 - $aSort or $aIndices is not an array
;                       2 - $aIndices has more entries than $aSort has columns
;                       3 - $aIndices contains a non-integer value
;                       4 - $aSort is not a 2D array
;                       5 - $aIndices contains an out-of-range column index
;                       6 - $aIndices contains duplicate column indices
;                       7 - internal _ArraySort call failed (@extended holds its @error)
; Author .........:     UEZ
; Version ........:     v0.90 build 2026-07-18 Beta
; =========================================================================================
Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0, $iStart = 0)
    If Not IsArray($aIndices) Or Not IsArray($aSort) Then Return SetError(1, 0, 0) ;checks if $aSort / $aIndices are arrays
    Local $iCols = UBound($aSort, 2)
    If $iCols = 0 Then Return SetError(4, 0, 0) ;array is 1D only
    Local $iKeys = UBound($aIndices)
    If $iKeys > $iCols Then Return SetError(2, 0, 0) ;check if $aIndices array is greater than $aSort column count
    Local $x, $m, $j, $k, $l, $bNewGroup
    Local $aIdx[$iKeys] ;local integer copy - caller's $aIndices stays untouched
    For $x = 0 To $iKeys - 1 ;validate array content
        If IsNumber($aIndices[$x]) Then
            If $aIndices[$x] <> Int($aIndices[$x]) Then Return SetError(3, 0, 0) ;fractional number
        ElseIf Not StringRegExp($aIndices[$x] & "", "^[-+]?\d+$") Then
            Return SetError(3, 0, 0) ;not an integer value
        EndIf
        $aIdx[$x] = Int($aIndices[$x])
        If $aIdx[$x] < 0 Or $aIdx[$x] > $iCols - 1 Then Return SetError(5, 0, 0) ;column index out of range
        For $m = 0 To $x - 1
            If $aIdx[$m] = $aIdx[$x] Then Return SetError(6, 0, 0) ;duplicate column index
        Next
    Next
    $iStart = Int($iStart) < 0 ? 0 : Int($iStart) > 1 ? 1 : Int($iStart)
    _ArraySort($aSort, $oDir, $iStart, 0, $aIdx[0]) ;initial sort by first key column
    If @error Then Return SetError(7, @error, 0)
    If $iKeys = 1 Then Return 1 ;only one index given -> done
    For $l = 0 To $iKeys - 2 ;one pass per additional key column
        $j = $iStart
        $k = $iStart + 1
        While $k < UBound($aSort)
            $bNewGroup = False
            For $m = 0 To $l ;compare ALL previous key columns, not just the current one
                If $aSort[$j][$aIdx[$m]] <> $aSort[$k][$aIdx[$m]] Then
                    $bNewGroup = True
                    ExitLoop
                EndIf
            Next
            If $bNewGroup Then
                If $k - $j > 1 Then
                    _ArraySort($aSort, $iDir, $j, $k - 1, $aIdx[$l + 1])
                    If @error Then Return SetError(7, @error, 0)
                EndIf
                $j = $k
            EndIf
            $k += 1
        WEnd
        If $k - $j > 1 Then ;last group at the end of the array
            _ArraySort($aSort, $iDir, $j, $k - 1, $aIdx[$l + 1])
            If @error Then Return SetError(7, @error, 0)
        EndIf
    Next
    Return 1
EndFunc   ;==>_ArraySort_MultiColumn

If you find a bug please report.

Br,

UEZ

Edited July 18 by UEZ
Code update

[gcue]

UEZ, that works perfectly!!!! many thanks =)

Try this:

expandcollapse popup

#include <Array.au3>
$max = 50
$s = ""
Dim $array[$max][4]
for $i = 0 to $max - 1
    $array[$i][0] = Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1))
    For $j = 0 To 2
        If Random(0, 1, 1) Then $s &= Chr(Random(48, 57, 1))
    Next
    If $s = "" Then $s = Chr(Random(65, 67, 1))
    $array[$i][1] =  $s
    $array[$i][2] = Random(1, 10, 1)
    $m = Random(1, 12, 1)
    $d = Random(1, 31, 1)
    If $m < 10 Then $m = "0" & $m
    If $d < 10 Then $d = "0" & $d
    $array[$i][3] = Random(1900, 2010, 1) &  "-" & $m & "-" & $d
    $s = ""
Next
_ArrayDisplay($array, 'Before:')
Dim $aI[3]
;order of sorting rows - here row 0,3,2
$aI[0] = 0
$aI[1] = 3
$aI[2] = 2

_ArraySort_MultiColumn($array, $aI)
_ArrayDisplay($array, 'After:')

; #FUNCTION# =============================================================================
; Name...........:      _ArraySort_MultiColumn
; Description ...:  sorts an array at given colums (multi colum sort)
; Syntax.........:      _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices)
; Parameters ....:  $aSort - array to sort
;                           $aIndices - array with colum indices which should be sorted in specified order - zero based
;                           $dir - sort direction - if set to 1, sort descendingly
; Author ........:      UEZ
; Version .......:      v0.60 build 2010-07-04 Beta
; =========================================================================================
Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0)
    Local $1st, $2nd
    If Not IsArray($aIndices) Or Not IsArray($aSort) Then ;checks if $aIndices is an array
        Return SetError(1, 0, 0) ;no array
    EndIf
    If UBound($aIndices) > UBound($aSort, 2) Then ;check if $aIndices array is greater the $aSort array
        Return SetError(2, 0, 0) ;$aIndices array is greater
    EndIf
    Local $x
    For $x = 0 To UBound($aIndices) - 1 ;check if array content makes sense
        If Not IsInt($aIndices[$x]) Then
            Return SetError(3, 0, 0) ;array content is not numeric
        EndIf
    Next
    If UBound($aIndices) = 1 Then ;check if only one index is given
        Return _ArraySort($aSort, $oDir, 0, 0, $aIndices[0])
    EndIf
    Local $j, $k, $l = 0
    _ArraySort($aSort, $oDir, 0, 0, $aIndices[0])
    Do
        $1st = $aIndices[$l]
        $2nd = $aIndices[$l + 1]
        $j = 0
        $k = 1
        While $k < UBound($aSort)
            If $aSort[$j][$1st] <> $aSort[$k][$1st] Then
                If $k - $j > 1  Then
                    _ArraySort($aSort, $iDir , $j, $k - 1, $2nd)
                    $j = $k
                Else
                    $j = $k
                EndIf
            EndIf
            $k += 1
        WEnd
        If $k - $j > 1 Then _ArraySort($aSort, $oDir, $j, $k, $2nd)
        $l += 1
    Until $l = UBound($aIndices) - 1
    Return SetError(0, 0, 1)
EndFunc

If you find a bug please report.

Br,

UEZ

[gcue]

hey Melba,

i was using Geosoft's udf _ArraySortClib to do my sorting before but could not get it to work with multi-sorting

http://dundats.mvps.org/autoit/udf_code.aspx?udf=arrayx

thanks!

gcue,

Two comments:

- 1. What have you tried so far?

- 2. You need to be much more specific as to the result you want. "sort first by column 1 then by column 2 then by column 3" can mean many things - my impression of what you are looking for is this:

A   A   A
A   A   B
A   B   A
A   B   B
B   A   A
B   A   B
B   B   A
B   B   B

Is that also what you envisage?

M23

[kescho]

Try this:

 

expandcollapse popup

#include <array.au3>
$max = 50
$s = ""
Dim $array[$max][4]
for $i = 0 to $max - 1
    $array[$i][0] = Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1)) & Chr(Random(65, 67, 1))
    For $j = 0 To 2
        If Random(0, 1, 1) Then $s &= Chr(Random(48, 57, 1))
    Next
    If $s = "" Then $s = Chr(Random(65, 67, 1))
    $array[$i][1] =  $s
    $array[$i][2] = Random(1, 10, 1)
    $m = Random(1, 12, 1)
    $d = Random(1, 31, 1)
    If $m < 10 Then $m = "0" & $m
    If $d < 10 Then $d = "0" & $d
    $array[$i][3] = Random(1900, 2010, 1) &  "-" & $m & "-" & $d
    $s = ""
Next
_ArrayDisplay($array, 'Before:')
Dim $aI[3]
;order of sorting rows - here row 0,3,2
$aI[0] = 0
$aI[1] = 3
$aI[2] = 2

_ArraySort_MultiColumn($array, $aI)
_ArrayDisplay($array, 'After:')

; #FUNCTION# =============================================================================
; Name.............:    _ArraySort_MultiColumn
; Description ...:  sorts an array at given colums (multi colum sort)
; Syntax...........:    _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices)
; Parameters ...:   $aSort - array to sort
;                           $aIndices - array with colum indices which should be sorted in specified order - zero based
;                           $dir - sort direction - if set to 1, sort descendingly
; Author .........: UEZ
; Version ........: v0.60 build 2011-04-19 Beta
; ========================================================================================
Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0)
    Local $1st, $2nd
    If Not IsArray($aIndices) Or Not IsArray($aSort) Then Return SetError(1, 0, 0) ;checks if $aIndices is an array
    If UBound($aIndices) > UBound($aSort, 2) Then Return SetError(2, 0, 0) ;check if $aIndices array is greater the $aSort array
    Local $x
    For $x = 0 To UBound($aIndices) - 1 ;check if array content makes sense
        If Not IsInt($aIndices[$x]) Then Return SetError(3, 0, 0) ;array content is not numeric
    Next
    If UBound($aIndices) = 1 Then Return _ArraySort($aSort, $oDir, 0, 0, $aIndices[0]) ;check if only one index is given
    Local $j, $k, $l = 0
    _ArraySort($aSort, $oDir, 0, 0, $aIndices[0])
    Do
        $1st = $aIndices[$l]
        $2nd = $aIndices[$l + 1]
        $j = 0
        $k = 1
        While $k < UBound($aSort)
            If $aSort[$j][$1st] <> $aSort[$k][$1st] Then
                If $k - $j > 1  Then
                    _ArraySort($aSort, $iDir , $j, $k - 1, $2nd)
                    $j = $k
                Else
                    $j = $k
                EndIf
            EndIf
            $k += 1
        WEnd
        If $k - $j > 1 Then _ArraySort($aSort, $oDir, $j, $k, $2nd)
        $l += 1
    Until $l = UBound($aIndices) - 1
    Return 1
EndFunc

If you find a bug please report.

Br,

UEZ

 

 

Hi UEZ!

I think I found a bug in your UDF.

If I have an array with 2 columns and when sorting descending by column 2 and also ascending by column 1 then all items are sorted as I want except the last one.

example:

col1  col2

10      3

11      3

12      3

10      2

11      2

12      2

12      1

11      1

10      1

The problem is commented in the code below:

WEnd
If $k - $j > 1 Then _ArraySort($aSort, $oDir, $j, $k, $2nd) ;<<<<<<<<<<<< $oDir has to be changed to $iDir
$l += 1

expandcollapse popup

; #FUNCTION# =============================================================================
; Name.............:    _ArraySort_MultiColumn
; Description ...:  sorts an array at given colums (multi colum sort)
; Syntax...........:    _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices)
; Parameters ...:   $aSort - array to sort
;                           $aIndices - array with colum indices which should be sorted in specified order - zero based
;                           $dir - sort direction - if set to 1, sort descendingly
; Author .........: UEZ
; Version ........: v0.60 build 2011-04-19 Beta
; ========================================================================================
Func _ArraySort_MultiColumn(ByRef $aSort, ByRef $aIndices, $oDir = 0, $iDir = 0)
    Local $1st, $2nd
    If Not IsArray($aIndices) Or Not IsArray($aSort) Then Return SetError(1, 0, 0) ;checks if $aIndices is an array
    If UBound($aIndices) > UBound($aSort, 2) Then Return SetError(2, 0, 0) ;check if $aIndices array is greater the $aSort array
    Local $x
    For $x = 0 To UBound($aIndices) - 1 ;check if array content makes sense
        If Not IsInt($aIndices[$x]) Then Return SetError(3, 0, 0) ;array content is not numeric
    Next
    If UBound($aIndices) = 1 Then Return _ArraySort($aSort, $oDir, 0, 0, $aIndices[0]) ;check if only one index is given
    Local $j, $k, $l = 0
    _ArraySort($aSort, $oDir, 0, 0, $aIndices[0])
    Do
        $1st = $aIndices[$l]
        $2nd = $aIndices[$l + 1]
        $j = 0
        $k = 1
        While $k < UBound($aSort)
            If $aSort[$j][$1st] <> $aSort[$k][$1st] Then
                If $k - $j > 1  Then
                    _ArraySort($aSort, $iDir , $j, $k - 1, $2nd)
                    $j = $k
                Else
                    $j = $k
                EndIf
            EndIf
            $k += 1
        WEnd
        If $k - $j > 1 Then _ArraySort($aSort, $oDir, $j, $k, $2nd) ;<<<<<<<<<<<< $oDir has to be changed to $iDir
        $l += 1
    Until $l = UBound($aIndices) - 1
    Return 1
EndFunc

It's only a small bug, but after correcting it everything works as it should.

Edited November 20, 2013 by kescho

[UEZ]

Thanks you very much kescho for reporting the bug!

I will check and modify the function appropriately.

Br,

UEZ