troubleshooting Question

Modifying Data In Query To Sort

Avatar of Bob Schneider
Bob SchneiderFlag for United States of America asked on
Microsoft SQL ServerASP
52 Comments24 Solutions189 ViewsLast Modified:
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)
		Set rs = Server.CreateObject("ADODB.Recordset")
		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
ASKER CERTIFIED SOLUTION
Big Monty
Web Ninja at large

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 24 Answers and 52 Comments.
Start Free Trial
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.
Unlock 24 Answers and 52 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros