Teacher(s) teaching the most students-CodePudding

I'm trying to write a query that shows which teacher(s) are teaching the most number of students but I can't seem to get past a syntax error.

When I run the code in the CTE only it appears to work fine.

Below is my test CASE and problem query. Can someone show me how what I need to do in order to fix this problem. In addition, if there is a better way to write the query I would welcome any suggestions. Would a PARTITION BY clause to RANK function be a cleaner solution?

Below is my test CASE and query. Thanks in advance to all who answer


CREATE TABLE teachers(teacher_id, first_name,  last_name) AS
   SELECT 101, 'Keith', 'Stein'  FROM dual UNION ALL
  SELECT 102,  'Roger',  'Wood' FROM dual UNION ALL
  SELECT 103,  'Douglas',  'Kern'   FROM dual UNION ALL
  SELECT 104, 'Paul',  'Weber'    FROM dual UNION ALL
  SELECT 105,  'Jeffrey',  'Lebowitz'    FROM dual UNION  ALL
  SELECT 106,  'Gabby',  'Orr'    FROM dual;


CREATE TABLE students(student_id, first_name,  last_name) AS
   SELECT 1, 'Faith', 'Aaron'  FROM dual UNION ALL
  SELECT 2,  'Lisa',  'Saladino' FROM dual UNION ALL
  SELECT 3,  'Leslee',  'Altman'   FROM dual UNION ALL
  SELECT 4, 'Patty',  'Kern'    FROM dual UNION ALL
  SELECT 5,  'Beth',  'Cooper'    FROM dual UNION  ALL
  SELECT 95,  'Zak',  'Despart'    FROM dual UNION  ALL
  SELECT 96,  'Owen',  'Balbert'    FROM dual UNION  ALL
   SELECT 97,  'Jack',  'Aprile'    FROM dual UNION  ALL
  SELECT 98,  'Nicole',  'Kramer'    FROM dual UNION  ALL
   SELECT 99,  'Jill',  'Coralnick'    FROM dual;


CREATE TABLE courses(course_id, course_name, teacher_id, semester) AS
SELECT 1, 'Geometry', 101, '2022-2' FROM DUAL UNION ALL
SELECT 2, 'Trigonometry', 102, '2022-2' FROM DUAL UNION ALL
SELECT 3, 'Calculus', 103, '2022-2' FROM DUAL UNION ALL
SELECT 4, 'Chemistry', 104, '2022-2' FROM DUAL UNION ALL 
SELECT 5, 'Biology', 105, '2022-2' FROM DUAL UNION ALL 
SELECT 6, 'Physcology', 106, '2022-2' FROM DUAL; 

CREATE TABLE student_courses (student_id,course_id) AS
SELECT 1, 1 FROM dual UNION ALL
SELECT 2, 1 FROM dual UNION ALL
SELECT 3, 1 FROM dual UNION ALL
SELECT 4, 1 FROM dual UNION ALL
SELECT 5, 1 FROM dual UNION ALL
SELECT 1, 2 FROM dual UNION ALL
SELECT 2, 2 FROM dual UNION ALL
SELECT 3, 2 FROM dual UNION ALL
SELECT 4, 2 FROM dual UNION ALL
SELECT 5, 2 FROM dual UNION ALL
SELECT 1, 3 FROM dual UNION ALL
SELECT 2, 3 FROM dual UNION ALL
SELECT 3, 3 FROM dual UNION ALL
SELECT 4, 3 FROM dual UNION ALL
SELECT 5, 3 FROM dual UNION ALL
SELECT 97, 1 FROM dual UNION ALL 
SELECT 97, 3 FROM dual UNION ALL 
SELECT 97, 5 FROM dual UNION ALL
SELECT 97, 6 FROM dual UNION ALL
SELECT 98, 3 FROM dual UNION ALL 
SELECT 98, 4 FROM dual UNION ALL
SELECT 98, 5 FROM dual UNION ALL
SELECT 99, 2 FROM dual UNION ALL 
SELECT 99, 4 FROM dual UNION ALL
SELECT 99, 5 FROM dual UNION ALL
SELECT 99, 6 FROM dual;


WITH teacher_student_rankings AS (
  SELECT
    t.teacher_id
    , t.first_name
    , t.last_name 
    , COUNT(DISTINCT sc.student_id) AS teacher_student_count
    , RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC) AS teacher_student_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
    LEFT JOIN student_courses sc
    ON c.course_id = sc.course_id
  GROUP BY
    t.teacher_id
    , t.first_name
    , t.last_name 
)
SELECT
  teacher_id
  , first_name
  , last_name 
FROM teacher_student_rankings
WHERE teacher_student_rank = 1:

CodePudding user response：

Please find the answer below

        WITH teacher_student_rankings AS (
      SELECT
        t.teacher_id
        , t.first_name
        , t.last_name 
        , COUNT(DISTINCT sc.student_id) AS teacher_student_count
        , RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC) AS teacher_student_rank
      FROM teachers t
        LEFT JOIN courses c
        ON t.teacher_id = c.teacher_id
        LEFT JOIN student_courses sc
        ON c.course_id = sc.course_id
      GROUP BY
        t.teacher_id
        , t.first_name
        , t.last_name 
    )
    SELECT
     t.teacher_id
        , t.first_name
        , t.last_name
        ,t.teacher_student_count as "No of students per teacher"
        ,t.teacher_student_rank
    FROM teacher_student_rankings t
    where teacher_student_rank =1;