ORA-00904: “GRADE_RANK”: invalid identifier

This query runs without grade_rank > 1 but I want only the results where grade_rank is > 1. I have tried using ROW_NUMBER() OVER() as grade_rank but it didn’t work.

SELECT
    student_id,
    section_id,
    grade_type_code,
    grade_code_occurrence,
    numeric_grade,
    ROW_NUMBER() OVER(
        PARTITION BY student_id, section_id, grade_type_code
        ORDER BY
            numeric_grade ASC
    )  grade_rank
FROM
    grade
WHERE
    (student_id = 102
    AND section_id = 86
    )

This is the result of the above query which is not what I quite want. I need the results with grade_rank > 1

Answer

Here’s one option using a subquery:

SELECT * 
FROM (
    SELECT
        employee_id,
        dept_id,
        pay_type,
        pay_code,
        pay_grade,
        ROW_NUMBER() OVER(
            PARTITION BY employee_id, dept_id, pay_type
            ORDER BY
                pay_grade ASC
        ) pay_rank
    FROM
        grade
    WHERE
        (employee_id = 99
        AND dept_id = 11)
) t
WHERE pay_rank > 1