ORA-00979: not a GROUP BY expression in Oracle-CodePudding

I can not execute this code on Oracle, the error shows:

"ORA-00979: not a GROUP BY expression"

However, I was able to run it successfully on MySQL.
How does this happen?

SELECT CONCAT(i.lname, i.fname) AS inst_name, 
           CONCAT(s.lname, s.fname) AS stu_name, 
           t.avg_grade AS stu_avg_grade
    FROM(
    SELECT instructor_id, student_id, AVG(grade) as avg_grade, RANK() OVER(PARTITION BY instructor_id ORDER BY grade DESC) AS rk
    FROM grade
    GROUP BY 1,2) t
    JOIN instructor i 
    ON t.instructor_id = i.instructor_id
    JOIN student s 
    ON s.student_id = t.student_id
    WHERE t.rk = 1
    ORDER BY 3 DESC

CodePudding user response：

You can't use ordinals like GROUP BY 1,2 in Oracle. In addition, the ORDER BY grade clause inside your RANK() function has a problem. Keep in mind that analytic functions evaluate after the GROUP BY aggregation, so grade is no longer available. Here is a version which should work without error:

SELECT CONCAT(i.lname, i.fname) AS inst_name,
       CONCAT(s.lname, s.fname) AS stu_name,
       t.avg_grade AS stu_avg_grade
FROM
(
    SELECT instructor_id, student_id, AVG(grade) AS avg_grade,
           RANK() OVER (PARTITION BY instructor_id ORDER BY AVG(grade) DESC) AS rk
    FROM grade
    GROUP BY instructor_id, student_id
 ) t
INNER JOIN instructor i 
    ON t.instructor_id = i.instructor_id
INNER JOIN student s 
    ON s.student_id = t.student_id
WHERE t.rk = 1
ORDER BY t.avg_grade DESC;