-
Nov 7th, 2017, 10:13 PM
#1
Thread Starter
Junior Member
[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
Last edited by rickabbam; Nov 7th, 2017 at 10:17 PM.
-
Nov 8th, 2017, 04:28 AM
#2
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()
-
Nov 8th, 2017, 05:22 AM
#3
Thread Starter
Junior Member
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
-
Nov 8th, 2017, 05:37 AM
#4
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)
-
Nov 8th, 2017, 05:47 AM
#5
Thread Starter
Junior Member
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
-
Nov 8th, 2017, 06:13 AM
#6
Re: How to group and rank with reference to another column

That seems to be an exact copy of your previous post.
-
Nov 8th, 2017, 07:00 AM
#7
Thread Starter
Junior Member
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
-
Nov 8th, 2017, 10:01 AM
#8
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
-
Nov 8th, 2017, 11:05 AM
#9
Thread Starter
Junior Member
Re: How to group and rank with reference to another column
-
Nov 8th, 2017, 01:58 PM
#10
Re: How to group and rank with reference to another column
 Originally Posted by rickabbam
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).
-
Nov 16th, 2017, 12:51 PM
#11
Thread Starter
Junior Member
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
-
Nov 16th, 2017, 12:53 PM
#12
Thread Starter
Junior Member
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
-
Nov 17th, 2017, 05:16 PM
#13
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.
-
Nov 23rd, 2017, 12:26 PM
#14
Thread Starter
Junior Member
Re: How to group and rank with reference to another column
 Originally Posted by si_the_geek
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()
-
Nov 23rd, 2017, 01:21 PM
#15
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).
-
Nov 28th, 2017, 05:13 PM
#16
Thread Starter
Junior Member
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

instead,wish it could be like

thank you
Last edited by rickabbam; Nov 28th, 2017 at 05:17 PM.
-
Nov 28th, 2017, 05:47 PM
#17
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|