Home » SQL & PL/SQL » SQL & PL/SQL » pagination (rownum) with the twist of reporting total count
pagination (rownum) with the twist of reporting total count [message #220362] Tue, 20 February 2007 07:00 Go to next message
Messages: 3
Registered: January 2007
Junior Member

Could anyone please advise on the most efficient way to combine pagination (rownum) *but* also report the total count of results?
Kind of like Google tells you: "showing results 21-30 out of 10000" ?

Or do you have to run 2 separate queries, one to extract the required page, and another to extract 'count(*)' ?
My 'select' is quite expensive, so I was hoping to avoid it.

Suppose you select all 'employees' who belong to 'HR', and show them in a web page, 10 employees per page, while also adding a message on the total results ( so in page 3 the message could be "showing results 21-30 out of total 1000 results" ).

In 2 queries it would be:

1. Select page 3:
select * from (
select e.*, rownum rw
from employees e
where e.dept_name='HR'
where rw between 21 and 30;

2. Count total results:
select count(*) from employees e
where where e.dept_name='HR';

Is there a better way ?
Thanks very much.
Re: pagination (rownum) with the twist of reporting total count [message #220383 is a reply to message #220362] Tue, 20 February 2007 08:31 Go to previous message
Messages: 118
Registered: February 2007
Senior Member
Google almost never tells you exact result it is approximation.
And approximation for example you can get by explaining query and getting expected cardinality out of it.
You can also search asktom.oracle.com for this question, he uses something like that.

Gints Plivna
Previous Topic: find bind values-not by tracing
Next Topic: Find results NOT in subquery
Goto Forum:

Current Time: Sat Mar 25 18:47:16 CDT 2017

Total time taken to generate the page: 0.14618 seconds