Get the total number of rows in a pagination request

Question

I have the following pagination request

SELECT * FROM (SELECT e.*, ROWNUM row_num FROM (SELECT emp_no, emp_name, dob from emp) outr WHERE ROWNUM < ( (pagenum * row_size) + 1)) WHERE row_num >= ( ( (pagenum - 1) * row_size) + 1) 

I would like to get the number of rows in the same query, and for that I tried using

 COUNT(*) OVER () 

however, I do not get accurate results when I paginate the next set of pages and lines.

How can I use COUNT(*) OVER () effectively?

Answer 1

A typical pagination request with the total number of rows would be:

 SELECT * FROM (SELECT outr.*, ROWNUM row_num FROM (SELECT emp_no, emp_name, dob, count(*) over () total_nb FROM emp ORDER BY ...) outr WHERE ROWNUM < ((pagenum * row_size) + 1)) WHERE row_num >= (((pagenum - 1) * row_size) + 1) 

Do not forget ORDER BY .