Using VB6.0, I have a school database that contain a field "English" and i have use SQL to create a recordset and have arranged English in Desc order. My problem is that i want to rank these student so that after a tie the next students gets a rank following a count of the tie as follows:
Eng: 45, 48, 67, 67, 67, 80, 80, 91.
English Ranks 91 - 1 80 - 2 80 - 2, 67 - 4, 67 - 4, 67 - 4, 48 - 7, 45 - 8,
Your question is not quite clear, but I guess you want something like this?
select Eng, rank() over (order by Eng desc) EnglishRank from somewhere
I guess you meant something along the lines of the following code. I am using just one way of getting the data out of the procedure. You will probably do something different.
Option Explicit
Private Type RankedScores
Rank As Long
Score As Long
End Type
Private Sub RankValues(ByRef the_rs As ADODB.Recordset, ByRef out_auRankedScores() As RankedScores)
Dim nIndex As Long
Dim nRank As Long
Dim nScore As Long
Dim nLastScore As Long
nRank = 0
nIndex = 0
' Resize the output buffer to its maximum size (won't work on some types of recordsets).
ReDim out_auRankedScores(1 To the_rs.RecordCount)
Do Until the_rs.EOF
nIndex = nIndex + 1
' Pull score out of the recordset. If it is not the same as the last score, then increment the rank.
nScore = CLng(the_rs.Fields.Item("English"))
If nScore <> nLastScore Then
nRank = nIndex
End If
' Write into output buffer.
With out_auRankedScores(nIndex)
.Rank = nRank
.Score = nScore
End With
' Reset last score.
nLastScore = nScore
' Next row.
the_rs.MoveNext
Loop
End Sub
