[RESOLVED] How to group and rank with reference to another column

[rickabbam]

Hello gurus
I need help in grouping or partitioning and ranking according to the subject. Every subject must have 1st to last student.
Am using the codes below with vb 2010


Dim TotaledRecords = From p In db.Assessments
Where p.Class = cboclass.Text And p.Stream = cbostream.Text
Select p
Order By p.Total Descending

For j = 1 To TotaledRecords.Count
TotaledRecords.ToList(j - 1).Position = j
Next
db.SubmitChanges()

I want it to restart the ranking with respect to the subject
thank you
See attached picture

[si_the_geek]

When you post code please put it inside code tags so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]

If I understand correctly, this will give the result you want:

        Dim TotaledRecords = From p In db.Assessments
                             Where p.Class = cboclass.Text And p.Stream = cbostream.Text
                             Select p
                             Order By p.Subject, p.Total Descending

        Dim previousSubject as String = ""
        Dim position as Integer = 0
        For j = 1 To TotaledRecords.Count
            If TotaledRecords.ToList(j - 1).Subject <> previousSubject Then
                previousSubject = TotaledRecords.ToList(j - 1).Subject
                position = 1
            Else 
                position += 1
            End If
            TotaledRecords.ToList(j - 1).Position = position
        Next
        db.SubmitChanges()

[rickabbam]

It worked as I expected Thank you
And is there a way or kindly assist me to add the 'st' ,'nd' ,'rd' and 'th' to the rankings

[si_the_geek]

I'm not aware of a built-in way, and unfortunately the rules aren't as simple as you might expect (eg: ending in 1 means 'st' unless it ends in 11, ending in 2 means 'nd' unless it ends in 12), so a 'simple' way to do it would be to create a function like this:

Private Function AppendThEtc(theNumber as Integer) as String
  Dim ending as String = ""

  Select Case theNumber Mod 100
  Case 11, 12, 13
      ending = "th"
  Case Else
      Select Case theNumber Mod 10
      Case 1
          ending = "st"
      Case 2
          ending = "nd"
      Case 3
          ending = "rd"
      Case 4
          ending = "th"
      End Select 
  End Select

  Return theNumber.ToString() & ending
End Function

usage:

            TotaledRecords.ToList(j - 1).Position = AppendThEtc(position)

[rickabbam]

It worked as I expected Thank you
And is there a way or kindly assist me to add the 'st' ,'nd' ,'rd' and 'th' to the rankings

[si_the_geek]

That seems to be an exact copy of your previous post.

[rickabbam]

Sorry sir,
Am new to this forum and my mobile network is not stable.

The st nd rd and th worked perfectly

Sir can u recommend book's title for me to study to develop and understand vb.net

Sir again how can I modify the code
To repeat the ranking number for the same marks as below

Marks ranking
45. 3rd
45. 3rd
85. 2nd
90. 1st
90 1st


Where will i click to mark to show that this thread is answered
Thank you

[Shaggy Hiker]

Finding a good book is tricky. If somebody has a recommendation, that's great, but you might also look at an online tutorial/reference, such as this:

http://www.homeandlearn.co.uk/NET/vbNet.html

[rickabbam]

Thank you for the link

[si_the_geek]

Sir again how can I modify the code
To repeat the ranking number for the same marks as below

Marks ranking
45. 3rd
45. 3rd
85. 2nd
90. 1st
90 1st

Only increase the position variable if the marks are different to the previous (hint: put an IF statement around the line: position += 1 , you may want to add a variable to keep track of the previous marks)

Where will i click to mark to show that this thread is answered
Thank you

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).

[rickabbam]

Ok sir,
I have been on it for a while
Can't figure it out

[rickabbam]

Ok sir,
I have been on it for a while
Can't figure it out
Please kindly help me out

[si_the_geek]

This is the code I showed before:

        Dim TotaledRecords = From p In db.Assessments
                             Where p.Class = cboclass.Text And p.Stream = cbostream.Text
                             Select p
                             Order By p.Subject, p.Total Descending

        Dim previousSubject as String = ""
        Dim position as Integer = 0
        For j = 1 To TotaledRecords.Count
            If TotaledRecords.ToList(j - 1).Subject <> previousSubject Then
                previousSubject = TotaledRecords.ToList(j - 1).Subject
                position = 1
            Else 
                position += 1
            End If
            TotaledRecords.ToList(j - 1).Position = position
        Next
        db.SubmitChanges()

...and this was the guide I gave:

Only increase the position variable if the marks are different to the previous (hint: put an IF statement around the line: position += 1 , you may want to add a variable to keep track of the previous marks)

so..

        Dim TotaledRecords = From p In db.Assessments
                             Where p.Class = cboclass.Text And p.Stream = cbostream.Text
                             Select p
                             Order By p.Subject, p.Total Descending

        Dim previousSubject as String = ""
        Dim previousMarks as SomeDataType
        Dim position as Integer = 0
        For j = 1 To TotaledRecords.Count
            If TotaledRecords.ToList(j - 1).Subject <> previousSubject Then
                previousSubject = TotaledRecords.ToList(j - 1).Subject
                position = 1
                previousMarks = ValueOfMarks
            Else 
                If previousMarks <> ValueOfMarks Then
                    position += 1
                End If
            End If
            TotaledRecords.ToList(j - 1).Position = position
        Next
        db.SubmitChanges()

I've left some parts in italics for you to work out.

[rickabbam]

This is the code I showed before:

        Dim TotaledRecords = From p In db.Assessments
                             Where p.Class = cboclass.Text And p.Stream = cbostream.Text
                             Select p
                             Order By p.Subject, p.Total Descending

        Dim previousSubject as String = ""
        Dim position as Integer = 0
        For j = 1 To TotaledRecords.Count
            If TotaledRecords.ToList(j - 1).Subject <> previousSubject Then
                previousSubject = TotaledRecords.ToList(j - 1).Subject
                position = 1
            Else 
                position += 1
            End If
            TotaledRecords.ToList(j - 1).Position = position
        Next
        db.SubmitChanges()

...and this was the guide I gave:


so..

        Dim TotaledRecords = From p In db.Assessments
                             Where p.Class = cboclass.Text And p.Stream = cbostream.Text
                             Select p
                             Order By p.Subject, p.Total Descending

        Dim previousSubject as String = ""
        Dim previousMarks as SomeDataType
        Dim position as Integer = 0
        For j = 1 To TotaledRecords.Count
            If TotaledRecords.ToList(j - 1).Subject <> previousSubject Then
                previousSubject = TotaledRecords.ToList(j - 1).Subject
                position = 1
                previousMarks = ValueOfMarks
            Else 
                If previousMarks <> ValueOfMarks Then
                    position += 1
                End If
            End If
            TotaledRecords.ToList(j - 1).Position = position
        Next
        db.SubmitChanges()

I've left some parts in italics for you to work out.

Is this correct

 Dim previousSubject as String = ""
 Dim previousMarks as String =""
Dim position as Integer = 0 
For j = 1 To TotaledRecords.Count 
If TotaledRecords.ToList(j - 1).Subject <> previousSubject Then
 previousSubject = TotaledRecords.ToList(j - 1).Subject position = 1
 previousMarks = TotaledRecords.ToList(j - 1).Marks
Else  
If previousMarks <> TotaledRecords.ToList(j-1) Then 
position += 1 
End If 
End If 
TotaledRecords.ToList(j - 1).Position = position 
Next 
db.SubmitChanges()

[si_the_geek]

That is something that you should not be asking, because the first step should be trying it - in which case Visual Studio will automatically tell you about most problems (along with details of what the problem is, and what line of code it is on).

In this case there is at least one problem which would be automatically shown to you, and that you should be able to fix yourself (check the highlights in my previous post, and what you've done). If you can't fix it yourself, you should be telling us specific details of the problem(s).

[rickabbam]

using these

Dim TotaledRecords = From p In db.Assessments 
Where p.Term = cboterm.Text And p.Year = cboyear.Text 
Select p Order By p.Subject, p.Total Descending 
Dim previousSubject As String = "" 
Dim previousTotal As Integer = 0
Dim position As Integer = 0 
For j = 1 To TotaledRecords.Count 
If TotaledRecords.ToList(j - 1).Subject <> previousSubject Then 
previousSubject = TotaledRecords.ToList(j - 1).Subject 
position = 1  
previousTotal = TotaledRecords.ToList(j - 1).Total
Else 
If previousTotal <> TotaledRecords.ToList(j - 1).Total Then 
position += 1 
End If 
End If TotaledRecords.ToList(j - 1).Position = position

this is what i get


instead,wish it could be like


thank you

[si_the_geek]

I'm afraid it isn't clear what you mean... while showing example output is good, explaining is also important.

I can think of several different things you might mean based on the pictures, but I'm not going to give up a chunk of my own time trying to amend the code you posted to suit each of the possibilities, in the hope that one of them is right (especially when the code you showed is only partial, and has had the indenting removed, and seems to be on a different track to the previous posts).