Sorting arrays and recordsets

[rplcmint]

Does any one have code out there to sort arrays or recordsets?

Here's the problem:

I'm bringing back a recordset from the database and loading a data grid. When someone clicks on the column, I want to be able to sort either ascending or descending. So I have to sort this recordset.

I don't want to have to call the stored procedure, I want to reduce the calls and place data on the client side.

Can anyone help or come up with a good alternative. Or there any data grids that do this for me?

I'd appreciate your help.

Mike

[jbart]

I found this code that uses an ADO Control on the form to sort by column in a dataGrid.

Private Sub DataGrid1_HeadClick(ByVal ColIndex As Integer)
    Adodc1.RecordSource = "Select * From user Order By " & _
    DataGrid1.Columns(ColIndex).DataField
    Adodc1.Refresh
End Sub

Maybe you could adapt it to your situation.

[MartinLiss]

If you are willing to change from a data grid to a ListView, the you can use the ListView's SortOrder property to sort automatically descending or ascending by any column you want. You can also allow the user to do the sorting whichever way he/she wants.

[jmcswain]

I adapted the QuickSort algorithm to sort any 2-dimensional array based on any column, assuming that the first dimension is the row and the second dimension is the column:

Array(0,0) = First row first column
Array(0,3) = First row fourth column

Creat a new module and paste all this code in. Then run TestSort from the immediate window to see it in action.

Option Explicit
Option Compare Text

Private mlngSort As Long ' Column # to sort on (first column = 0)
Private mlngColFirst As Long ' LBound of second index
Private mlngColLast As Long ' UBound of second index

Public Function TestSort()
    Dim lngRow As Long
    Dim lngCol As Long
    Dim MyArray() As Variant
    
    Randomize Timer
    ReDim MyArray(10 To 19, 0 To 3)
    For lngRow = LBound(MyArray, 1) To UBound(MyArray, 1)
        MyArray(lngRow, 0) = lngRow
        MyArray(lngRow, 1) = Int((199 - 100 + 1) * Rnd + 100)
        MyArray(lngRow, 2) = MyArray(lngRow, 1)
        MyArray(lngRow, 3) = Int((199 - 100 + 1) * Rnd + 100)
        Debug.Print lngRow & ": " & MyArray(lngRow, 0) & ", " & MyArray(lngRow, 1) & ", " & MyArray(lngRow, 2) & ", " & MyArray(lngRow, 3)
    Next
    SortArray MyArray, 2
    Debug.Print "Sorted"
    For lngRow = LBound(MyArray, 1) To UBound(MyArray, 1)
        Debug.Print lngRow & ": " & MyArray(lngRow, 0) & ", " & MyArray(lngRow, 1) & ", " & MyArray(lngRow, 2) & ", " & MyArray(lngRow, 3)
    Next
End Function

' Entry Function
Public Function SortArray(ByRef pvarArray As Variant, ByVal plngSortColumn As Long)
    mlngSort = plngSortColumn
    mlngColFirst = LBound(pvarArray, 2)
    mlngColLast = UBound(pvarArray, 2)
    ReDim mvarExchange(mlngColFirst To mlngColLast)
    QuickSort pvarArray, LBound(pvarArray, 1), UBound(pvarArray, 1)
End Function

Private Function QuickSort(ByRef pvarArray As Variant, ByVal plngLeft As Long, ByVal plngRight As Long)
    Dim lng As Long
    Dim lngFirst As Long
    Dim lngLast As Long
    Dim varMid As Variant
    Dim varExchange As Variant
    
    lngFirst = plngLeft
    lngLast = plngRight
    varMid = pvarArray((plngLeft + plngRight) / 2, mlngSort)
    Do
        Do While pvarArray(lngFirst, mlngSort) < varMid And lngFirst < plngRight
            lngFirst = lngFirst + 1
        Loop
        Do While varMid < pvarArray(lngLast, mlngSort) And lngLast > plngLeft
            lngLast = lngLast - 1
        Loop
        If lngFirst <= lngLast Then
            For lng = mlngColFirst To mlngColLast
                varExchange = pvarArray(lngFirst, lng)
                pvarArray(lngFirst, lng) = pvarArray(lngLast, lng)
                pvarArray(lngLast, lng) = varExchange
            Next
            lngFirst = lngFirst + 1
            lngLast = lngLast - 1
        End If
    Loop Until lngFirst > lngLast
    If plngLeft < lngLast Then QuickSort pvarArray, plngLeft, lngLast
    If lngFirst < plngRight Then QuickSort pvarArray, lngFirst, plngRight
End Function