dokuwiki

Verschachtelte with-clause zum “Nachbauen” von limit

with
emp_sal_sort as (
select first_name,
       last_name,
       salary
from employees 
order by salary desc
),
emp_sal_sort_rn as (
select rownum rn, emp_sal_sort.* from emp_sal_sort
)
select * from emp_sal_sort_rn
where rn > 10 and rn <= 20

select first_name, last_name, salary from employees where
department_id in  
  (select i from 
     (select department_id i, count(*) from employees
      group by department_id 
      having count(*) >= 5)
   )

select rownum, rn, first_name, last_name, salary from (
select rownum rn, first_name, last_name, salary from employees )
where rn > 5

select * from (
select rownum r, 
       first_name, 
       last_name,
       salary 
 from (
  select first_name, 
       last_name,
       salary
       from employees
       order by salary desc
 )
 ) where r > 5