Selecting best two marks from a list of 3

  • Thread starter Thread starter cro
  • Start date Start date
C

cro

This is a problem related to a system to take the best two
marks froma choice of three modules sat by a student. The
additional problem is that the student can take one module
twice in which case the highest mark can be chosen.
However this only leaves one more opportunity where a
second different module must be sat and in this case
regardless of the mark for that second module that score
must be taken.

Eg1
Module 1 score 45
Module 1 score 67
Module 2 score 38 In this senario the score of 67 must be
taken and has to be added to the score of 38

Eg2 Module 1 67
 
To get the highest mark a student received in each module, create a Totals
query. Group by StudentID and ModuleID, and ask for Max of Score.

To eliminate the lowest one, you need a subquery in the WHERE clause. The
subquery will be similar the the main query, grouping by StudentID,
ModuleID, with Max of Score, WHERE the StudentID is the same as in the main
query, and ordered by Max(Score) DESC. That puts the student's lowest mark
on top, and you want the TOP 1. SELECT the ModuleID. In the main query, the
WHERE clause will ask for the other modules, i.e.:
WHERE ModuleID <> (SELECT TOP 1 ModuleID FROM ...

You will end up with something like this:

SELECT a.StudentID, a.ModuleID, Max(a.Score) AS MaxOfScore FROM a
WHERE a.ModuleID <>
(SELECT TOP 1 b.ModuleID FROM a AS b
WHERE b.StudentID = a.StudentID
GROUP BY b.StudentID, b.ModuleID
ORDER BY Max(b.Score) DESC, b.ModuleID )
GROUP BY a.StudentID, a.ModuleID;
 
What do 'a' and 'b' relate to? I assume that 'a' relates to the original table but what about 'b'

----- Allen Browne wrote: ----

To get the highest mark a student received in each module, create a Total
query. Group by StudentID and ModuleID, and ask for Max of Score

To eliminate the lowest one, you need a subquery in the WHERE clause. Th
subquery will be similar the the main query, grouping by StudentID
ModuleID, with Max of Score, WHERE the StudentID is the same as in the mai
query, and ordered by Max(Score) DESC. That puts the student's lowest mar
on top, and you want the TOP 1. SELECT the ModuleID. In the main query, th
WHERE clause will ask for the other modules, i.e.
WHERE ModuleID <> (SELECT TOP 1 ModuleID FROM ..

You will end up with something like this

SELECT a.StudentID, a.ModuleID, Max(a.Score) AS MaxOfScore FROM
WHERE a.ModuleID <
(SELECT TOP 1 b.ModuleID FROM a AS
WHERE b.StudentID = a.StudentI
GROUP BY b.StudentID, b.ModuleI
ORDER BY Max(b.Score) DESC, b.ModuleID
GROUP BY a.StudentID, a.ModuleID
 
Yes. Replace "a" with your table name.

Leave the "b" in place.

The query needs to refer to two instances of the same table, so "b" is just
an alias for the same table. In the subquery you see:
(SELECT ... FROM a AS b WHERE ...
The "AS b" bit identifies this 2nd instance of table "a" with the alias "b".
 
Back
Top