troubleshooting Question

# Modifying Data In Query To Sort asked on
Microsoft SQL ServerASP
I have a results query that gets sent to finishers of a race shortly after they finish.  They have two times, a chip time and a gun time.  They are usually a little bit different.  I want to be able to sort the data based on one of the two times listed (chip or gun).  Currently I sort by event place get the data using ReDim Preserve, and then do a bubble sort after the fact.  Is there a better way?  The root problem is that the times are in there as text ("00:12:34.567").  In order to do a good sort I convert them to seconds as I pull them out and then sort.  I am looking for a better approach.  Here is my code:

``````		x = 0
ReDim ThisArr(2, 0)
sql = "SELECT ParticipantID, FnlTime, ChipTime FROM IndResults WHERE RaceID = " & lRaceID & " ORDER BY EventPl"
rs.Open sql, conn, 1, 2
Do While Not rs.EOF
ThisArr(0, x) = rs(0).Value
ThisArr(1, x) = ConvertToSeconds(rs(1).Value)
ThisArr(2, x) = ConvertToSeconds(rs(2).Value)
x = x + 1
ReDim Preserve ThisArr(2, x)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

're-order results
For y = 0 To UBound(ThisArr, 2) - 2
For m = y + 1 To UBound(ThisArr, 2) - 1
If sRsltsSort = "chip" Then
If CSng(ThisArr(2, y)) > CSng(ThisArr(2, m)) Then
For n = 0 To 2
TempArr(n) = ThisArr(n, y)
ThisArr(n, y) = ThisArr(n, m)
ThisArr(n, m) = TempArr(n)
Next
End If
Else
If CSng(ThisArr(1, y)) > CSng(ThisArr(1, m)) Then
For n = 0 To 2
TempArr(n) = ThisArr(n, y)
ThisArr(n, y) = ThisArr(n, m)
ThisArr(n, m) = TempArr(n)
Next
End If
End If
Next
Next

For x = 0 To UBound(ThisArr, 2) - 1
iMyOverallPl = CInt(iMyOverallPl) + 1
If CLng(ThisArr(0, x)) = CLng(lPartID) Then Exit For
Next
``````
Big Monty
Web Ninja at large
###### Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.