Hi all,

I have a database of marks and I need to find out the maximum mark taken by each student in any subject. For example If am a student and I have got 60 in Language, 70 in Maths, 80 in Science and 90 in Social Studies, I would need the result "90" and the relevant Social Science subject to be displayed so that I know am good at Social Science of all the subjects. Each student may have different subjects.

The table name is marks. The fields are subject_id, mark_obtained, student_id.

When I try this query.

Select subject_id, max(mark_obtained) from marks group by student_id

In this case, I get the correct maximum mark, but the subject id displayed is incorrect. Can anyone tell me what am doing wrong here?

you may need to consider usign a "group by" clause to uniqeuly identify each student or subject depending on what you are looking for

If I give just

Select max(mark_obtained) from marks group by student_id

it returns the maximum mark each student scored. How do I find out the subject in which each student had secured this mark?

Select max(mark_obtained) from marks group by student_id, Subject_ID
OR
Select max(mark_obtained) from marks group by Subject_ID, student_id

both of those queries will return a result set that looks like this --

56
73
86
75
24
90
55
64

might be accurate but hardly what i would call useful

how about this --Edit: fixed error in query