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 CTE is destroyed, and you can’t refer anymore.

with rows_and_position as 
  ( 
    select emp_id,
    last_name, 
    salary,
    dep_id,
    row_number() over (partition by dep_id order by salary desc) as position
    from employee
  )
select dep_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