VS 2010 How to group and rank with reference to another column-VBForums
Results 1 to 13 of 13

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    8

    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: 128
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,359

    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
    New Member
    Join Date
    Nov 2017
    Posts
    8

    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,359

    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
    New Member
    Join Date
    Nov 2017
    Posts
    8

    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,359

    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
    New Member
    Join Date
    Nov 2017
    Posts
    8

    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
    30,984

    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
    New Member
    Join Date
    Nov 2017
    Posts
    8

    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,359

    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
    New Member
    Join Date
    Nov 2017
    Posts
    8

    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
    New Member
    Join Date
    Nov 2017
    Posts
    8

    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,359

    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.

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

Survey posted by VBForums.