Click here to Skip to main content
11,496,146 members (677 online)
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
See more: VB.NET
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 1:22am
Edited 8-Nov-12 1:27am
v2
Comments
Member 10767830 at 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 at 8-May-14 8:44am
   
my email mahfuz_57@yahoo.co.in

please help me..
if you only want to display then use gridview and order by query. And from gridview index no. you got the rank.
  Permalink  
Comments
savedlema at 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 at 9-Nov-12 17:32pm
   
Thank you...But, how do I get the grid view row index? Its not shown by default...
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).
  Permalink  
Comments
savedlema at 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 at 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!
see this example
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!
Smile | :)
  Permalink  
v2
Comments
savedlema at 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.
Yes, you can use order by ( marks ) clause to sort your students then select any by row index.

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

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

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

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]
  Permalink  
v2
Comments
savedlema at 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 at 9-Nov-12 4:13am
   
i have updated the solution please check , accept it if it helps.
savedlema at 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 at 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 at 10-Nov-12 16:31pm
   
Sory,for now I use Access (.mdb)..anything?
psychic6000 at 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 at 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 at 13-Nov-12 18:01pm
   
I managed to do it psychic6000. Thank you very much for your continued support here.
Blessings to you.

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

  Print Answers RSS
0 RyanDev 230
1 Sergey Alexandrovich Kryukov 114
2 Richard Deeming 100
3 OriginalGriff 89
4 Mostafa Asaduzzaman 59
0 Sergey Alexandrovich Kryukov 10,401
1 OriginalGriff 8,910
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100