CODEWITHSUNDEEP
vb6
user1462748
user1462748

Reputation: 1

How to sort arrays

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,

Upvotes: 0

Views: 431

Answers (2)

Mark Bertenshaw
Mark Bertenshaw

Reputation: 5689

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

Upvotes: 0

Dante May Code
Dante May Code

Reputation: 11247

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

Upvotes: 2

Related Questions