CODEWITHSUNDEEP
oracle-databasepagination
v1shnu
v1shnu

Reputation: 2231

oracle sql pagination with total pages or total entries

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.

Upvotes: 1

Views: 4430

Answers (2)

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 2

Boneist
Boneist

Reputation: 23588

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.

Upvotes: 5

Related Questions