Paging in the Oracle database with help of SQL Queries

Paging in the Oracle database with help of SQL Queries

With Different programming help, we need to pass the parameter to fetch the no of rows and starting point in oracle 12c sql queries.

In Oracle 12c, For Paging Query developed is little easy.
We can use the OFFSET and FETCH keyword to defined the Paging with help of SQL Queries

SELECT * FROM tables
ORDER BY column_id
OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;

Example

--For First 10 rows
SELECT employee_id,first_name,last_name FROM hr.employees
ORDER BY employee_id
OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        101 Neena                Kochhar
        102 Lex                  De Haan
        103 Alexander            Hunold
        104 Bruce                Ernst
        105 David                Austin
        106 Valli                Pataballa
        107 Diana                Lorentz
        108 Nancy                Greenberg
        109 Daniel               Faviet
        110 John                 Chen

--For next 10 ROWS, you need to change offset to 10
SELECT employee_id,first_name,last_name FROM hr.employees
ORDER BY employee_id
OFFSET 11 ROWS FETCH NEXT 10 ROWS ONLY;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        111 Ismael               Sciarra
        112 Jose Manuel          Urman
        113 Luis                 Popp
        114 Den                  Raphaely
        115 Alexander            Khoo
        116 Shelli               Baida
        117 Sigal                Tobias
        118 Guy                  Himuro
        119 Karen                Colmenares
        120 Matthew              Weiss

Paging with help of Row_Number
With help of different languages, you have to pass the parameter n and m

Before 12c, we can use row_number for using paging functionality by taking one unique column in order by clause.

select * from (
select col_id,col_name,row_number() over (order by col_id) n
from table_name)
where rn between :n and :m order by rn;

Example:

SELECT * FROM
(select employee_id,first_name,last_name,row_number() over (order by employee_id) rn
from hr.employees) where rn between 1 and 10 order by rn;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                         RN
----------- -------------------- ------------------------- ----------
        100 Steven               King                               1
        101 Neena                Kochhar                            2
        102 Lex                  De Haan                            3
        103 Alexander            Hunold                             4
        104 Bruce                Ernst                              5
        105 David                Austin                             6
        106 Valli                Pataballa                          7
        107 Diana                Lorentz                            8
        108 Nancy                Greenberg                          9
        109 Daniel               Faviet                            10

-- For next 10 rows, you can change between values
SELECT * FROM
(select employee_id,first_name,last_name,row_number() over (order by employee_id) rn
from hr.employees) where rn between 11 and 20 order by rn;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                         RN
----------- -------------------- ------------------------- ----------
        110 John                 Chen                              11
        111 Ismael               Sciarra                           12
        112 Jose Manuel          Urman                             13
        113 Luis                 Popp                              14
        114 Den                  Raphaely                          15
        115 Alexander            Khoo                              16
        116 Shelli               Baida                             17
        117 Sigal                Tobias                            18
        118 Guy                  Himuro                            19
        119 Karen                Colmenares                        20

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.