11. Advanced Searching

Paginating Through a Result Set

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