Oracle - Analytic functions

Getting total count of rows for the query and required number of records for the page in single query using oracle analytic functions.

to get all the employee records whose first name is "peter" and display total count and per page display only 10 records

 Simple table Structure as follows

CREATE TABLE Employee (

emp_id number not null,

first_name varchar2 not null,

last_name varchar2 not null,

join_date Date,

);

the query would look like the following.

select * from (select max(ROWNUM) over() totalCount, row_number() over(order by join_date asc) rownumb,allrows.* from (select emp_id,first_name,last_name,join_date from Employee where first_name like '%peter%') allrows ) where rownumb between X and Y

where x and y are starting and ending row numbers, it varies depending upon the page index.

here totalCount is the total number of rows that exist in db for the query.