MIN(column) with subquery

greatest-n-per-grouporacle

How do I query 3 tables (STUDENTS, MODULES, MARKS) such that I return the module name and the lowest mark score for student John Doe?

SQL> DESC STUDENTS;
 Name                    Null?    Type
 STUDENT_NO                       VARCHAR2(10)
 SURNAME                          VARCHAR2(20)
 FORENAME                         VARCHAR2(20)

SQL> DESC MODULES;
 Name                    Null?    Type
 MODULE_CODE                      VARCHAR2(8)
 MODULE_NAME                      VARCHAR2(20)

SQL> DESC MARKS;
 Name                    Null?    Type
 STUDENT_NO                       VARCHAR2(10)
 MODULE_CODE                      VARCHAR2(8)
 MARK                             NUMBER(38)

I have:

SQL> SELECT MOD.MODULE_NAME, MA.MARK 
    FROM MODULES MOD 
        INNER JOIN MARKS MA ON(MOD.MODULE_CODE = MA.MODULE_CODE) 
    WHERE MA.STUDENT_NO = (SELECT S.STUDENT_NO FROM STUDENTS S WHERE S.SURNAME = 'Doe');

MODULE_NAME MARK Databases 80 Programming 65 Operating Systems 50

but cannot get it to display the lowest value.
Trying to put a MIN(MA.MARKS) in the SELECT clause or after the WHERE clause causes an error.

Best Answer

Why would this not be sufficient?

SELECT * FROM (
  SELECT MOD.MODULE_NAME, MA.MARK
  FROM MODULES MOD 
  JOIN MARKS MA ON (MOD.MODULE_CODE = MA.MODULE_CODE) 
  JOIN STUDENTS S ON (S.STUDENT_NO = MA.STUDENT_NO)
  WHERE S.SURNAME = 'Doe'
  ORDER BY 2) x
WHERE ROWNUM = 1;