Sorting an array on multiple dimensions

[mcpeekj]

Got another one for you guys...

I have some client activity data in a MSSQL db. Unfortunately the clients are references by codes and the translations are only available in another MSSQL db on a different server (don't ask).

So my solution to this is to dump the translations into an array, then dump the activity data into an array and replace the client codes with the translations in the activity array.

My problem is that I then need to sort my activity array by multiple fields (which would have been easy to do in the original query), but can't find any scripts anywhere to do that. I can only sort on 1 field at a time.

Any thoughts on how I can sort on multiple columns? Should I do something like drop the translated array back into a recordset and sort there (don't know if that's possible or not)? Should I go about this some totally different way?

Joining the tables across the different servers is not an option.

 

[Sheco]

Should I do something like drop the translated array back into a recordset and sort there (don't know if that's possible or not)?

It is possible.

Another possibility: instead of pulling both recordsets out into arrays you could iterate through the RS with the data "codes" in a Do While loop and, each time through the loop, set the .Filter property of the other RS. Something like:
Do While Not rsCodes.EoF
rsTranslation.Filter = "Code = " & rsCodes("Code")
If rsTranslation.RecordCount = 1 Then
Response.Write rsCodes("Code") & " = " & rsTranslation("Description") & "<br>" & vbCrLf
End If
rsCodes.MoveNext
Loop

 

[mcpeekj]

I started out with the nested recordsets, but the performance was absolutely terrible. That's why I switched to arrays.

Any other ideas? (or how I can get the array into a fake recordset?)

 

[mcpeekj]

I started out with the nested recordsets, but the performance was absolutely terrible. That's why I switched to arrays.

Any other ideas? (or how I can get the array into a fake recordset?)