We want to paginate a result set. For example, we want to return the first five rows from a table, then the next five, and so forth.
There is no concept of first, last, or next in SQL, and we must impose order on the rows needed.
with series as
(
select rownum id, level*10 data from dual
connect by level <= 10
)
select * from series where id between 1 and 5;
Skipping n Rows from a Table
We want to skip the second or fourth or nth row from a result set.
To skip the second or fourth or nth row from a result set, we must impose order on the result set
-- Skip even-numbered rows
select ename
from (
select row_number() over (order by ename) rn,
ename
from emp
) x
where mod(rn,2) = 1
Incorporating OR Logic When Using Outer Joins
We want to return the name and department information for all employees in departments 10 and 20 along with department information for departments 30 and 40 (but no employee information).
-- Solution 1
select e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
on (d.deptno = e.deptno and (e.deptno=10 or e.deptno=20))
order by 2
-- Solution 2
SELECT
e.ename,
d.deptno,
d.dname,
d.loc
FROM
dept d
LEFT JOIN (
SELECT
ename,
deptno
FROM
emp
WHERE
deptno IN ( 10, 20 )
) e ON ( e.deptno = d.deptno )
ORDER BY
2
Selecting the Top n Records
We want to limit a result set to a specific number of records based on a ranking of some sort. For example, you want to return the names and salaries of the employees with the top five salaries.
-- Top five salaries employee
select ename,sal
from (
select ename, sal,
dense_rank() over (order by sal desc) dr
from emp
) x
where dr <= 5
-- Fetch first 5 rows from the employees table ordered by hire_date
SELECT
employee_id,
first_name,
last_name,
hire_date
FROM
employees
ORDER BY
hire_date
FETCH FIRST 5 ROWS ONLY;
Finding Records with the Highest and Lowest Values
We want to find the employees with the highest and lowest salaries in table EMP.
select ename
from (
select ename, sal,
min(sal)over() min_sal,
max(sal)over() max_sal
from emp
) x
where sal in (min_sal,max_sal)
Investigating Future Rows
We want to find any employees who earn less than the employee hired immediately after them. Based on the following result set: ENAME SAL HIREDATE
ENAME SAL HIREDATE
---------- ---------- ---------
SMITH 800 17-DEC-80
ALLEN 1600 20-FEB-81
WARD 1250 22-FEB-81
JONES 2975 02-APR-81
BLAKE 2850 01-MAY-81
CLARK 2450 09-JUN-81
TURNER 1500 08-SEP-81
MARTIN 1250 28-SEP-81
KING 5000 17-NOV-81
JAMES 950 03-DEC-81
FORD 3000 03-DEC-81
MILLER 1300 23-JAN-82
SCOTT 3000 09-DEC-82
ADAMS 1100 12-JAN-83
SMITH, WARD, MARTIN, JAMES, and MILLER earn less than the person hired immediately after they were hired, so those are the employees you want to find with a query.
SELECT
ename,
sal,
hiredate
FROM
(
SELECT
ename,
sal,
hiredate,
LEAD(sal) OVER( ORDER BY hiredate ) next_sal
FROM
emp
) alias
WHERE
sal < next_sal
Shifting Row Values
We want to return each employee’s name and salary along with the next highest and lowest salaries. If there are no higher or lower salaries, then results to wrap (first SAL shows last SAL and vice versa)
Sample result set
select ename,sal,
coalesce(lead(sal)over(order by sal),min(sal)over()) forward,
coalesce(lag(sal)over(order by sal),max(sal)over()) rewind
from emp
Ranking Results
We want to rank the salaries in table EMP like below while allowing for ties.
-- To allow for ties, use the window function DENSE_RANK OVER
select dense_rank() over(order by sal) rnk, sal
from emp
Suppressing Duplicates
We want to find the distinct different job types in table EMP
-- Solution 1
select distinct job
from emp;
-- Solution 2
select job
from (
select job,
row_number()over(partition by job order by job) rn
from emp
) x
where rn = 1
Finding Knight Values
We want to return a result set that contains each employee’s name, the department they work in, their salary, the date they were hired, and the salary of the last employee hired, in each department.
Sample result set
select deptno,
ename,
sal,
hiredate,
max(sal) keep(dense_rank last order by hiredate) over(partition by deptno) latest_sal
from emp
order by 1, 4 desc
Generating Simple Forecasts
We want to return additional rows and columns representing future actions. For example, consider the following input data. We want to return three rows per row returned in the result set (each row plus two additional rows for each order). Along with the extra rows, we would like to return two additional columns providing dates for expected order processing.
Order takes two days to process. Verification occurs one day after processing, and shipment occurs one day after verification.
Sample Input
Sample Output
WITH nrows AS (
SELECT
level n
FROM
dual
CONNECT BY
level <= 3
)
SELECT
id,
order_date,
process_date,
CASE
WHEN nrows.n >= 2 THEN
process_date + 1
ELSE
NULL
END AS verified,
CASE
WHEN nrows.n = 3 THEN
process_date + 2
ELSE
NULL
END AS shipped
FROM
(
SELECT
nrows.n id,
sysdate + nrows.n AS order_date,
sysdate + nrows.n + 2 AS process_date
FROM
nrows
) orders,
nrows
