order multi-dimensional array by Number, text

[pjani]

I want to sort an array by two fields. first on number in column1 and then text in column2.

please help me with any ideas or sample code which does this?

I can dump the array in a temporaty table in database. Then can run select * from TempTable order by col1, col2. Then i will have to drop the table. This has a lot of overhead. So I want a function to do this order by col1, col2.

Thank you.

 

[sunaj]

Hi,

You can use the code below to sort a 2D array by one column.
You could call this code to sort the array by the first column, pick out the subarrays where the first column has identical values and then call the sub again for each subarray to sort by the 2nd column.

I don't know if this is easier or more efficient that the Database solution.

Sunaj



------------------------------------------------------------
Sub BubbleSort(iArray As Variant, SortBy As Integer)
Dim lLoop1 As Long
Dim lLoop2 As Long
Dim lLoop3 As Long
Dim lTemp As Long
For lLoop1 = UBound(iArray, 2) To LBound(iArray, 2) Step -1
For lLoop2 = LBound(iArray, 2) + 1 To lLoop1
If iArray(SortBy, lLoop2 - 1) > iArray(SortBy, lLoop2) Then
For lLoop3 = 0 To UBound(iArray)
lTemp = iArray(lLoop3, lLoop2 - 1)
iArray(lLoop3, lLoop2 - 1) = iArray(lLoop3, lLoop2)
iArray(lLoop3, lLoop2) = lTemp
Next lLoop3
End If
Next lLoop2
Next lLoop1
End Sub
------------------------------------------------------------

 

[99mel]

In the MSDN theres a statment called ASORT. This sorts all the elements in an array into ascending order. Is that not part of VB or is there a library or something which needs adding to your vb project?

 

[MichaelRed]

Well, this is one of those (rare?) instances where some knowledge of the problem details can provide different useful soloutions.

First, if the number of unique items in the first key is small relative to the the total number of items (and this is KNOWN to always be the case), then a "heap sort" would be an ideal soloution. Just use the first key element to assign the records to their heap. Sort each heap acording to the method of choice, and reassemble the heaps in order of the primary key.

Next. If the ratio of (unique) primary keys to the (unique) secondary keys is not known or "fixed", then I would expect that placing all entries into a recordset where the primary key is pre-set to the two fields would be as efficient as "just doing a set of sorts".

Finally. If the number of items (records) to sort is (more-or-less) unbound, then creating a recordset could be actually faster than attempting to sort an array in "memory", as the "Memory" could easily be the virtual type (e.g. it is really disc access masquerading as SLOOOOOOOOOOOw memory).

Aberant thoughts. The only real drawback to the recordset approach is the time required. This is partially a function of the dbengine and partially a function of the mechanical time delay associated with doing disc access. The disc access part can be generally overcome by using ADO recordsets. Just create the ADO recordset from the array. It SHOULD have almost NO disc access (depending on the number of records). ADO is generally a bit slower than gneral ODBC (after all, it is just a 'wrapper' for ODBC), however I believe the overhead would be a lot less than the traditional DAO process used before VB6.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over