How to use Analytic Functions with ROWNUM In SQL

In this approach, we combine analytic functions with ROWNUM to achieve pagination.

Syntax:

SELECT employee_id, first_name, last_name, salary
FROM (
    SELECT employee_id, first_name, last_name, salary, ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num
    FROM employees
)
WHERE row_num BETWEEN 1 AND 3;

Explanation:

• We use a subquery to assign row numbers to each record based on the desired ordering (in this case, ORDER BY employee_id).
• Then, we select the columns we need along with the row numbers from the subquery.
• Finally, we filter the results using the ROW_NUM column to retrieve the desired page of results (in this case, rows 1 to 3).

Example: Pagination using Analytic Functions with ROWNUM

SELECT employee_id, first_name, last_name, salary
FROM (
    SELECT employee_id, first_name, last_name, salary, ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num
    FROM employees
)
WHERE row_num BETWEEN 1 AND 3;

Output:

Explanation: This query retrieves the first 3 records from the employees table sorted by employee_id using analytic functions.

Pagination is a technique used to divide large datasets into smaller, manageable chunks called pages. It’s important for improving user experience and optimizing database performance. In PL/SQL, pagination can be achieved using various methods, allowing users to navigate through query results efficiently.

In this article, we’ll explore how to efficiently manage large datasets in PL/SQL using pagination techniques. We’ll cover three methods: ROW_NUMBER() with Subqueries, Cursors with OFFSET and LIMIT, and Analytic Functions with ROWNUM. Each method is explained with examples to provide a clear understanding.