oracle sql pagination with total pages or total entries

Question

I'm trying to access paginated results from a DB. Following the below query:

SELECT * FROM
(
    SELECT a.*, rownum r__
    FROM
    (
        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

from here , I am able to achieve pagination.

I modified the query like below inorder to get total counts as well :

SELECT * FROM
(
    SELECT a.*, rownum r__ , count(OrderDate)
    FROM
    (
        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a    group by OrderDate
) 
WHERE r__ between (pageNumber * pageSize )+ 1 and (pageNumber *pageSize) + pageSize;

But I also want to calculate the total number of pages and if the requested page is the last page as well. I tried to get the COUNT of the inner query so that I can calculate the total pages inside my code, but I could not get it. I've tried by COUNT(ORDERDATE) and grouping but it does not work.

Please tell me how I can achieve this.

Boneist · Accepted Answer · 2017-07-21T14:55:43.193

I would use analytic functions to do the work you're trying to do, e.g.:

SELECT res.*,
       CEIL(total_num_rows/pagesize) total_num_pages
FROM   (SELECT o.*,
               row_number() OVER (ORDER BY orderdate DESC, shippingdate DESC) rn,
               COUNT(*) OVER () total_num_rows
        FROM   orders o
        WHERE  customerid LIKE 'A%') res
WHERE  rn BETWEEN (pagenumber - 1) * pagesize + 1 AND pagenumber * pagesize;

N.B. untested.

The query does the pagination by using the row_number() analytic function to assign a number to each row, across the specified groups and in the specified order. Since we don't have a partition by clause in the OVER section, the function is working across the entire set of rows.

Similarly, we use the count() analytic function to get the count of the rows across the specified groups - and again, as we haven't specified any partition by clause, the function works across the entire set of rows.

Now you have those numbers, it's a simple matter to do the pagination and find the total number of pages.

Amazing ! This works as expected and also gives me the total pages. Thank you so much :) — v1shnu, Jul 24 '17 at 13:22

score 2 · Answer 2 · answered Jul 22 '17 at 08:20

2

If you are on Oracle 12.1 or later, this becomes simpler:

select o.*
     , 'Page ' || pagenumber || ' of ' || ceil(count(*) over () / pagesize) as pagecount
from   orders o
where  customerid like 'T%'
order by orderdate desc, shippingdate desc
offset (pagenumber -1) * pagesize rows fetch next pagesize rows only;

The page count column will report values like Page 2 of 34.

answered Jul 22 '17 at 08:20

William Robertson

15,273
4
38
44

Unforntunately, we are still on an older version. Hopefully, this might be of use to people having the same requirement with that version. – v1shnu Jul 24 '17 at 13:24

oracle sql pagination with total pages or total entries

2 Answers2