How to Write a Common Table Expression

A common table expression (also called CTE) is the result set of a select which is stored as an intermediate table, during the query execution. You can refer to the CTE name in the query as any other valid table. However after the query ends, the table is destroyed, and cannot be referred anymore.

with rows_and_position as 
    ( 
        select  emp_id,
                last_name, 
                salary,
                dept_id,
                row_number() over (partition by dep_id order by salary desc) as position
        from employee
    )
select dept_id, last_name, salary
from  rows_and_position
where position = 1

In the previous query rows_and_position is a CTE and can be referenced as a regular table in the outer select.

IN THIS PAGE