Querying A Join Table

[AlistairH]

First time visit here and looking forward to picking up a few pointers with regard to database integration within a web environment.

I have one particular issue at the moment and being new to MySQL is causing me problems so I'll just jump straight in there if I may.

I have access to a database table which lists exams together with student results. It is essentially a join table between one containing the exam details and one containing student details. I do not have access to the individual tables

Essentially, the data I can access comprises of a joint primary key comprising 'ExamID' and StudentID together with the student's mark for each exam, not surprisingly called 'Mark'

I want to be able to list, for each exam, the student that got the top mark - or the 2nd or 3rd for that matter.

Initially, I thought I'd simply use a SELECT DISTINCT ExamID statement to stick the exam IDs into a PHP array through which I could loop running another SELECT statement similar to

SELECT StudentID FROM 'tblresults' WHERE ExamID = ExamArrayIndex ORDER BY Mark LIMIT 0, 1

The ExamArrayIndex would be the current value of ExamID taken from the array as I loop through.

This seems a rather inefficient way of doing this and I wonder if there was a way to combine the two sql statements to achieve the same ends.

Any guidance would be appreciated

Thanks

Alistair

[genevish]

This will get you the top result:

SELECT max(mark), examid, name FROM tblresults r join tblstudents s on r.studentid = s.studentid group by examid

[smantscheff]

This is a bit tricky, but with a subselect it can be done:

select ExamId,StudentId,Mark from marks where (ExamId,Mark) in (select ExamId as e, min(mark) as mm from marks group by ExamId);

The proposal of genevish is no solution since it refers to tables which are not accessible.

Always include a complete test case which makes it a lot easier to view into your problem. Here follows one in which I replaced the IDs by text for readability reasons.

create table marks 
(ExamID varchar(255)
,StudentId varchar(255)
,mark integer
,unique(ExamId,StudentId)
);
insert into marks values
('physics','dinky',1),
('physics','bofur',1),
('physics','gollum',2),
('art','bofur',5),
('art','dinky',8),
('art','gollum',1),
('math','dinky',1),
('math','bofur',2);


select ExamId,StudentId,Mark from marks where (ExamId,Mark) in (select ExamId as e, min(mark) as mm from marks group by ExamId);

+---------+-----------+------+
| ExamId  | StudentId | Mark |
+---------+-----------+------+
| physics | dinky     |    1 |
| physics | bofur     |    1 |
| art     | gollum    |    1 |
| math    | dinky     |    1 |
+---------+-----------+------+

[AlistairH]

Thanks for the feedback.

I've had to go away on business so I won't be able to test this until the weekend. One thing I did notice though is, in the solution from smantscheff the min/max can be used to find the top and bottom performing students in each subject, how can I list those students who were 2nd or 3rd best - or worst for that matter?

[smantscheff]

The obvious solution would be

select ExamId,StudentId,Mark from marks where (ExamId,Mark) in 
	(select ExamId as e, mark as m from marks 
		order by mark 
		limit 2
	);

But MySQL replies with an error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

From a practical point of view it's only a minor inefficency to do two queries, one for the distinct ExamIds and one for each ExamId group, as you proposed. But I must concede that it would be more elegant to have it all in one query.

Also the question arises which results you would like to have. Do you want to limit the number of exams returned or the the number of distinct marks?
Example: If you want the two best results, are this in my example (physics,dinky,1) and (physics,bofur,1). Or does (physics,gollum,2) also belong to the result because mark=2 is the 2nd best result?

Maybe you could design a nested subquery which utilizes the min/max aggregate function but excludes the values already consumed one level above.