Calculate student rank in class?

Question

0.00/5 (No votes)

See more:

I want to ask one thing; I have a table where I keep values for students marks (StudentID,Marks). Let's say there are 50 students. I want to be able to display on the form the class rank for each student (who is 1st,2nd,3r.....50th) according the marks.

MY IDEA: If I load these marks & studentID to a dataset by using a SELECT statement with ORDER BY (or what?), I hope the marks will be listed in an Ascending/Descending order, so the first at the top, the last at the bottom. If that's correct, then can I obtain the student rank by obtaining his row number in the dataset?
For example, in

dataset.Tables("StudentRank").Rows (4).Item (2)

This will be for a student in rank/position 5 from the top. Now, how can I get the value contained in .Rows (4) and post it somewhere or use it anywhere?

I hope I explained myself well.

I will apreciate any help please.

PS: I mean, how can know the row number of a record in the dataset? My main point is ranking though.

Posted 8-Nov-12 0:22am

savedlema

Updated 8-Nov-12 0:27am

v2

Add a Solution

Comments

Member 10767830 8-May-14 8:38am

Hello..!! i have a table contains terms like 1st term , 2nd term etc with session 2023, 2014,.. etc, how can i calculate rank of a student for particular session and term..?? please experts help me..!! plsss..

Member 10767830 8-May-14 8:44am

my email mahfuz_57@yahoo.co.in

please help me..

4 solutions

Solution 2

if you only want to display then use gridview and order by query. And from gridview index no. you got the rank.

Posted 8-Nov-12 0:28am

faisal23

Comments

savedlema 8-Nov-12 16:36pm

Thank you for responding. But, this is what I want:After performing a SELECT with ORDER BY query, the results will be filled to a dataset ("StudentRank").So, let's say I search for a particular student, (say John), from that, I want to get John's position in the class.

In other words, John may probably be stored in dataset.Tables ("StudentRank").Rows (4).Item (2), that will means John is in position 5 (4+1) in the class. Now, I don't want the dataset.Tables ("StudentRank").Rows(4).Item(2), I just want to display Johns marks on the form and also display his rank (5) in the class. Something like this:

StudentID: G23
Physics:80
Biology:70
Chemistry:90
Total:240
Average: 70
Rank in the class:5 (I can solve the above, this is the problem now)
All in all, my point is to be able to determine and display student rank in a class. I have tables for each subject (tblPhysics,tblChemistry...) and a table for a specific class (tblClass1) which will contain (StudentID,Physics,Chemistry.... and display the average score for each student)
I hope I made myself clearer, and thank you for your response.

savedlema 9-Nov-12 17:32pm

Thank you...But, how do I get the grid view row index? Its not shown by default...

Solution 3

Yes, you may use the ORDER BY clause to obtain the list of ordered student ranks.

Quote:
dataset.Tables("StudentRank").Rows (4).Item (2)

This looks correct to me.

Quote:
PS: I mean, how can know the row number of a record in the dataset?

Probably you meant the record number in a DataTable. The record number is the row index (as far as I know there is no intrinsic record number).

Posted 8-Nov-12 0:32am

CPallini

Comments

savedlema 8-Nov-12 16:32pm

Thank you for responding. But, this is what I want:After performing a SELECT with ORDER BY query, the results will be filled to a dataset ("StudentRank").So, let's say I search for a particular student, (say John), from that, I want to get John's position in the class.

In other words, John may probably be stored in dataset.Tables ("StudentRank").Rows (4).Item (2), that will means John is in position 5 (4+1) in the class. Now, I don't want the dataset.Tables ("StudentRank").Rows(4).Item(2), I just want to display Johns marks on the form and also display his rank (5) in the class. Something like this:

StudentID: G23
Physics:80
Biology:70
Chemistry:90
Total:240
Average: 70
Rank in the class:5 (I can solve the above, this is the problem now)
I hope I made myself clearer, and thank you for your response.

savedlema 9-Nov-12 17:33pm

Well, thanks. But, how do I get the row number, for instance, from a GridView? If I can get a row number that could be helpful.
Thanks!

Solution 4

see this example

SQL

select student_Id,marks,dense_rank() over(order by marks desc) as Rank
from
(
    select 1 as student_Id, 100 as marks
    union all
    select 2 as student_Id, 100 as marks
    union all
    select 3 as student_Id, 80 as marks
)
as a

Happy Coding!
:)

Posted 8-Nov-12 0:40am

Aarti Meswania

v2

Comments

savedlema 8-Nov-12 16:35pm

Thank you for responding. But, this is what I want:After performing a SELECT with ORDER BY query, the results will be filled to a dataset ("StudentRank").So, let's say I search for a particular student, (say John), from that, I want to get John's position in the class.

In other words, John may probably be stored in dataset.Tables ("StudentRank").Rows (4).Item (2), that will means John is in position 5 (4+1) in the class. Now, I don't want the dataset.Tables ("StudentRank").Rows(4).Item(2), I just want to display Johns marks on the form and also display his rank (5) in the class. Something like this:

StudentID: G23
Physics:80
Biology:70
Chemistry:90
Total:240
Average: 70
Rank in the class:5 (I can solve the above, this is the problem now)
All in all, my point is to be able to determine and display student rank in a class. I have tables for each subject (tblPhysics,tblChemistry...) and a table for a specific class (tblClass1) which will contain (StudentID,Physics,Chemistry.... and display the average score for each student)
I hope I made myself clearer, and thank you for your response.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

psychic6000 · Accepted Answer · 2012-11-08T01:08:00

Solution 5

Yes, you can use order by ( marks ) clause to sort your students then select any by row index.

VB

dataset.Tables("StudentRank").Rows (4).Item (2)

is okay if dataset is an identifier, (in that case, not a good choice to use a keyword as identifier)

you can use this code to get your row

VB

Dim dt As New DataSet
Dim rankStudent As DataRow = dt.Tables("Student Rank").Rows(4)

if you are searching for a student for example with student name

VB

Dim rankStudent As DataRow = dt.Tables("Student Rank").Rows(4)
For Each d As DataRow In dt.Tables("Student Rank").Rows
    If String.Compare(d.Item(0), "studentname") = 0 Then
        rankStudent = 
        Exit For
    End If
Next

once you have your target record, you can do anything you want with it.

i prefer if your project has datatype (StudentRank) of your table, then use that as a type of row, that will allow you to access column names as properties of rankstudents
e.g
dim rankstudent as [projectname].[datasetname].[table name with word row]

addition to solution:
in your database query/ select, you can use following to add row number (as rank)

SQL

SELECT ROW_NUMBER() 
        OVER (ORDER BY StudentMarks desc) AS Rank, 
    StudentId as rollNO, StudentName, StudentMarks
FROM Students

output would be like:
rank rollno studentName StudentMarks
1 y jack [maximum marks]
2 x jill [less than maximum marks but more than everyone else]

now if you want to have someone jill's rank you will use

SQL

SELECT * FROM
     (SELECT ROW_NUMBER() 
        OVER (ORDER BY StudentMarks desc) AS Rank, 
    StudentId as rollNO, StudentName, StudentMarks
FROM Students) AS std
WHERE StudentName='Jill'

output would be
rank rollno studentName StudentMarks
2 x jill [jill's Marks]

Posted 8-Nov-12 1:08am

psychic6000

Updated 8-Nov-12 22:11pm

v2

Comments

savedlema 8-Nov-12 16:36pm

Thank you for responding. But, this is what I want:After performing a SELECT with ORDER BY query, the results will be filled to a dataset ("StudentRank").So, let's say I search for a particular student, (say John), from that, I want to get John's position in the class.

In other words, John may probably be stored in dataset.Tables ("StudentRank").Rows (4).Item (2), that will means John is in position 5 (4+1) in the class. Now, I don't want the dataset.Tables ("StudentRank").Rows(4).Item(2), I just want to display Johns marks on the form and also display his rank (5) in the class. Something like this:

StudentID: G23
Physics:80
Biology:70
Chemistry:90
Total:240
Average: 70
Rank in the class:5 (I can solve the above, this is the problem now)
All in all, my point is to be able to determine and display student rank in a class. I have tables for each subject (tblPhysics,tblChemistry...) and a table for a specific class (tblClass1) which will contain (StudentID,Physics,Chemistry.... and display the average score for each student)
I hope I made myself clearer, and thank you for your response.

psychic6000 9-Nov-12 4:13am

i have updated the solution please check , accept it if it helps.

savedlema 9-Nov-12 17:21pm

psychic6000, Thank you very much for responding. I tried this:

sqlQuery = "SELECT ROW_NUMBER () " _
+ "OVER (ORDER BY Average desc) AS Rank," _
+ "StudentId as rollNO,Physics,Chemistry" _
+ "FROM tblClass1"

and got this error message "Syntax error (missing operator) in query expression 'ROW_NUMBER () OVER (ORDER BY Average desc)'.". Can you tell which operator is missing? If the code works, I think my problem will be solved.

Thanks so much so far.

psychic6000 10-Nov-12 8:56am

it works fine... you are using sql version? row_number is not supported with sql 2000, it was introduced in 2005...

savedlema 10-Nov-12 16:31pm

Sory,for now I use Access (.mdb)..anything?

psychic6000 11-Nov-12 5:47am

Then use a temporary table. (It will cast you a lot of performance) which has a auto number column and a studentID/name column (use all of the columns if you want), now insert values from you main table like
Insert into tempTable(student name) select studentName from studentTable order by marks desc
This will generate a ranked student record column
Now select your record of student from there. That's it.
Next time please specify all the details with your question.

savedlema 12-Nov-12 22:00pm

Thank you so much. And, Sorry for not being specific at first. Now,please, Why do I get an error creating a temporary table with :"INSERT INTO #temp SELECT StudentID, Average FROM tblForm1 ORDER BY Average DESC " OR even as simple as this:"CREATE TABLE #Results"? But this "CREATE TABLE Results" works. I thought we put # when we need to create temporary tables (Access), its like I've once done that. Help on that please?

savedlema 13-Nov-12 18:01pm

I managed to do it psychic6000. Thank you very much for your continued support here.
Blessings to you.