Pagination/Paging with SELECT query in Oracle

Example of using Paging SQL Query in Oracle

Get row N through M from many rows of table.

WITH t AS (
SELECT employee_id,ROW_NUMBER() over (ORDER BY employee_id) rn
FROM employees
)
SELECT employee_id FROM t WHERE rn BETWEEN 11 AND 15;

EMPLOYEE_ID
-----------
        110
        111
        112
        113
        114

Getting some rows from in-between as used in Paging:
Example fetching rows from 21 to 25, you can make a loop in programming or increment on button press:


-- in Oracle 12c
SELECT employee_id,first_name FROM employees ORDER BY employee_Id
OFFSET 20 ROWS FETCH NEXT 5 ROWS ONLY;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        120 Matthew
        121 Adam
        122 Payam
        123 Shanta
        124 Kevin

--older version:

SELECT employee_id,first_name FROM (SELECT employee_id,first_name,
ROW_NUMBER() over (ORDER BY employee_id) RowNumber
 FROM employees)
WHERE RowNumber BETWEEN 21 AND 25;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        120 Matthew
        121 Adam
        122 Payam
        123 Shanta
        124 Kevin

Get N number of rows: first n or last n by using DESC clause:

Example show you getting last n no of rows:


--In Oracle 12c, Introduced FETCH data
SQL> select employee_id from employees order by employee_id desc fetch first 5 row only;

EMPLOYEE_ID
-----------
        206
        205
        204
        203
        202 
		
--Before 12c use rownum

SELECT * FROM ( SELECT employee_id FROM employees ORDER BY employee_id DESC
) WHERE ROWNUM <= 5;

EMPLOYEE_ID

-----------
        206
        205
        204
        203
        202

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.