2

I cannot work this out, how do I get the subquery to return the lowest course weight for each lecturer, it currently only returns the lowest and is my subquery wrong or outer query?

Question: Show each lecturer’s lowest coursework weighting, displaying the Staff ID, the Module ID and the weighting selected.

SELECT m1.ModuleID, m1.CWWeight, l.StaffID
FROM dbo.module as m1
INNER JOIN dbo.Lecturer as l
    ON m1.ModuleConvenor = l.StaffID
WHERE m1.CWWeight = (SELECT MIN(m2.CWWeight)
                    FROM dbo.Module as m2)
  • 1
    You have tagged the question both MySQL and SQL Server. Based on dbo I assume the latter but please only use tags if you are certain they apply. – Aaron Bertrand May 29 at 23:58
4

There are many many ways to do this. Here is one that only slightly changes your attempt:

SELECT m1.ModuleID, m1.CWWeight, l.StaffID
FROM dbo.module as m1
INNER JOIN dbo.Lecturer as l
    ON m1.ModuleConvenor = l.StaffID
WHERE m1.CWWeight = (SELECT MIN(m2.CWWeight)
                    FROM dbo.Module as m2
                    WHERE m2.ModuleConvenor = l.StaffID) ;
4

Another approach, which is easily extensible for things like getting the lowest 3, the highest 5, the min or the max or both, etc.:

;WITH m AS 
(
   SELECT ModuleID, CWWeight, StaffID = ModuleConvenor,
     rn = ROW_NUMBER() OVER (PARTITION BY ModuleConvenor ORDER BY CWWeight)
   FROM dbo.module
)
SELECT m.ModuleID, m.CWWeight, l.StaffID
  FROM m INNER JOIN dbo.Lecturer AS l
  ON m.StaffID = l.StaffID
  WHERE m.rn = 1;

The other thing I like about this pattern is that if there is a chance for ties, you can break it by adding clauses to the ORDER BY. For example, if two modules share the lowest weight, this would pick the one with the higher ModuleID:

OVER (PARTITION BY ModuleConvenor ORDER BY CWWeight, ModuleID DESC)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.