VS 2010 [RESOLVED] How to group and rank with reference to another column-VBForums
Results 1 to 17 of 17

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

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

    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
    Name:  help to code.jpg
Views: 167
Size:  36.0 KB
    Last edited by rickabbam; Nov 7th, 2017 at 11:17 PM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to group and rank with reference to another column

    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:
    Code:
            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()

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

    Re: How to group and rank with reference to another column

    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

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to group and rank with reference to another column

    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:

    Code:
    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:
    Code:
                TotaledRecords.ToList(j - 1).Position = AppendThEtc(position)

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

    Re: How to group and rank with reference to another column

    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

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to group and rank with reference to another column



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

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

    Cool Re: How to group and rank with reference to another column

    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

  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    31,548

    Re: How to group and rank with reference to another column

    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
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

    Re: How to group and rank with reference to another column

    Thank you for the link

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to group and rank with reference to another column

    Quote Originally Posted by rickabbam View Post
    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).

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

    Re: How to group and rank with reference to another column

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

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

    Re: How to group and rank with reference to another column

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

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to group and rank with reference to another column

    This is the code I showed before:
    Code:
            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..
    Code:
            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.

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

    Re: How to group and rank with reference to another column

    Quote Originally Posted by si_the_geek View Post
    This is the code I showed before:
    Code:
            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..
    Code:
            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
    Code:
     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()

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to group and rank with reference to another column

    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).

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    16

    Re: How to group and rank with reference to another column

    using these
    Code:
    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
    Name:  onlinqq.jpg
Views: 17
Size:  14.2 KB

    instead,wish it could be like
    Name:  onlin.jpg
Views: 17
Size:  15.5 KB

    thank you
    Last edited by rickabbam; Nov 28th, 2017 at 06:17 PM.

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,581

    Re: How to group and rank with reference to another column

    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width