> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/11.-advanced-searching.md).

# 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.

{% hint style="info" %}
There is no concept of first, last, or next in SQL, and we must impose order on the rows needed.
{% endhint %}

```
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.

{% hint style="info" %}
To skip the second or fourth or nth row from a result set, we must impose order on the result set
{% endhint %}

```
-- 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

<figure><img src="/files/q6nNCmvapWAvoHFgSL7c" alt="" width="278"><figcaption></figcaption></figure>

```
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.

<figure><img src="/files/qLqcc2DBDYDbqRnip7HI" alt=""><figcaption></figcaption></figure>

```
-- 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

<figure><img src="/files/CL0iZdZMwvbBO9f9PrMJ" alt="" width="335"><figcaption></figcaption></figure>

```
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.

{% hint style="info" %}
Order takes two days to process. Verification occurs one day after processing, and shipment occurs one day after verification.
{% endhint %}

Sample Input

<figure><img src="/files/M1V1cNzpqUnFyLvci2gx" alt="" width="173"><figcaption></figcaption></figure>

Sample Output

<figure><img src="/files/J83fgo8U4qcSKoTq5CFq" alt="" width="339"><figcaption></figcaption></figure>

```
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
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/11.-advanced-searching.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.