Fetch students marks in database and say who is 1st,2nd,3rd......?

[savedlema]

Hi friends, thanks all for your contributions here at DaniWeb Community.
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.

[Reverend Jim]

We'd have to see the structure of the table. Is there one record per student? Are there multiple records where the multiple records have to be added before comparing to other students? Are the grades stored as numbers or letters? The structure of the table will determine the format of the query.

It is also important to know what database you are using. For example, MS SQL supports the RANK OVER clause which returns ranking numbers. This is important because if you want to know the top five students you can't just do an ORDER BY and pick the top 5. You may have (because of ties) more than five people with the top five grades. RANK OVER will account for that.

[savedlema]

Thanks Reverend.
I use Access database (.mdb),For this case,

I have tables for each subject i.e tblPhysics,tblChemistry,tblBiology.. (tables contain StudentID,Test,Final,Total,Average,Grade). From the form (frmScores), I can record student marks for test and final, calculate total and average and determine the grade (A,B,C,D..), and post these values to a table for a specific subject (whethere its tblPhysics,tblBiology or what)

When I came to ranking students, I thought the best approach would be to post the total marks from the frmScores form to another table tblClass1 which will contain total subject marks for each student, so the table will look like this:
tblClass1 (StudentID,Physics,Chemistry,Biology,English....), in this table, I will store subject marks (integers) for each subject per each student (StudentID).

So then, I want to be able to sum up scores for each student, then compare the sum with other students in this table and determine the ranking.
Or, is there any other easy way for me to do this?
I hope I'm clearer.

Thanks for any inputs.

[Reverend Jim]

The following query does the calculation and orders the results from top marks to lowest marks

SELECT StudentID, Physics+English+Chemistry+Biology+History AS totalMarks
  FROM tblClass1
 ORDER BY totalMarks DESC

To pick the top five you can use

SELECT TOP 5 StudentID, Physics+English+Chemistry+Biology+History AS totalMarks
  FROM tblClass1
 ORDER BY totalMarks DESC

but keep in mind that you may have more than five students with the top five marks.

[savedlema]

Thanks Reverend!
Look, I can perform a SELECT...ORDER BY that can fill the dataset and then the datagrid view like I did to the gridview whose image I have attached here:

As you can see, G1 will be number 1 student (ranking), G2=2,G5=5 and G3=4. My problem is, to be able to get the rank number for a particular student (so that I can include it in a student report), for example, get rank=3 for G5 student. From the datagrid I have now, I will have to manually count to tell the student's rank in the class, this is tedious when there are many students in the class.

I wish the rows in the dataset were numbered, then I could get the rank number from there. Any suggestion on how to do this?

Thanks again.

[savedlema]

Reverend, do you have an idea with this:

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

It is bringing an error message: "Syntax error (missing operator) in query expression 'ROW_NUMBER() OVER (ORDER BY StudentMarks desc)'."

I wonder if someone out there can fix that.

[Reverend Jim]

I'm not that familiar with Access.

[marcel.vandersluys.3]

You could insert your results in a temp table with a fake identity field. The identity matches the row number
create table #tmp(id identity(1,1), otherfields nvarchar(50), ......)
insert into #tmp (otherfields, .....)
select .......

Select * from #tmp where id=2

[TrojanhorseRovhine]

"SELECT TOP 10 SCORE ,STUD_NAME,USERNAME FROM tblPlayer ORDER BY SCORE DESC" THANKS FOR THIS ANSWER IT HELPS A LOT