Finding maximum values across fields

Question

I have a database and it manages student data. I would like to find  top most values across fields. Fields are subjects and I have to use two highest values to find a certain value. I have used a code to define maximum function but this works only in finding top most, how can i adjust it to find the next highest or next two highest.

Answer 1

I did accordingly, but i find it difficult to calculate final grade, cos even after getting the top scores there is need for additions to be done to get the final aggregate. Can i use code?

Answer 2

As it happens you'll find a simple example of this sort of model as Ranking.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file is principally intended to show how to rank per group, in this case ranking the students per subject.  The model is a very simple one:

As Scott explained you'd need to add a TermID column to the StudentCourses table in the demo, along with a referenced Terms table, so that it would represent a ternary (3-way) relationship type between students, courses and terms, rather than the binary (2-way) relationship type between students and courses as at present.

Answer 3

and this is where the subjects are fields,

Now with studentsubject table,won't it give a problem since that would mean more many fields(being students).

No, you are missing the point here. A Relational database should be tall and thin, not wide. You define data with records not field names. So you should not have a table with field for each subject. You should have a record for each student/subject/score. So if John took Math and got a score of 75, that is ONE record defined by the studentID, the subjectID and the score. If John got scores for 5 subjects he would have 5 records. 

You may need to add another field to this table to define what term John received a score for in that subject. So we are talking about 4-5 fields:

StudentSubjectID

StudentID

SubjectID

TermID

Score

Answer 4

Thanks for the reply.

Well I have student table with student details, exams-with scores and this is where the subjects are fields, then subject table which actually has no relationship with student table. Now with studentsubject table,won't it give a problem since that would mean more many fields(being students).

Answer 5

I have a database and it manages student data. I would like to find  top most values across fields. Fields are subjectsand I have to use two highest values to find a certain value. I have used a code to define maximum function but this works only in finding top most, how can i adjust it to find the next highest or next two highest.

This would be a lot easier if your database was properly normalized. Notice the bolded portion above. That is not the right way to design this. You should have at least three tables; 

Students: info identifying each student

Subjects: a listing of each subject

StudentSubjects: a record for each student and the scores for each subject. (If a student had 3 subjects there would be 3 records)

With this structure it would be very easy to sort scores within each student.

I suggest you look at redesigning your database.