Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

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