I am working in a database that has three tables
1. Candidates (CandidateID, Name, Marks)
2. Schools (SchoolID, School Name, VacantPosts)
3. CandidatesFor (SchoolID, CandidateID)
Candidates table contains the data of persons who have applied for a vacant post in the schools. Schools table contains the data of schools along with number of vacant posts.
CandidatesFor table has two fields “SchoolID” and “CandidateID”. One Candidate may be linked with at most five schools. (i.e. one candidate may have applied in five schools for recruitment)
I want to pick unique candidate for each school that has maximum marks. Can you help me in this problem?