How can I get the total result count, and a given subset ('page' of results) with the same SQL Query with Oracle

Question

I would like to display a table of results. The data is sourced from a SQL query on an Oracle database. I would like to show the results one page (say, 10 records) at a time, minimising the actual data being sent to the front-end.

At the same time, I would like to show the total number of possible results (say, showing 1-10 of 123), and to allow for pagination (say, to calculate that 10 per page, 123 results, therefore 13 pages).

I can get the total number of results with a single count query.

SELECT count(*) AS NUM_RESULTS FROM ... etc.

and I can get the desired subset with another query

SELECT * FROM ... etc. WHERE ? <= ROWNUM AND ROWNUM < ?

But, is there a way to get all the relevant details in one single query?

Update Actually, the above query using ROWNUM seems to work for 0 - 10, but not for 10 - 20, so how can I do that too?

score 1 · Answer 1 · edited Aug 21 '17 at 12:36

1

You can use inner join on your table and fetch total number of result in your subquery. The example of an query is as follows:

SELECT E.emp_name, E.emp_age, E.emp_sal, E.emp_count
  FROM EMP as E 
 INNER JOIN (SELECT emp_name, COUNT(*) As emp_count
               FROM EMP GROUP BY emp_name) AS T
    ON E.emp_name = T.emp_name WHERE E.emp_age < 35;

edited Aug 21 '17 at 12:36

Udo Held

12,314
11
67
93

answered Apr 04 '17 at 16:09

Chiranjeevi Paidi

11
1

score 1 · Answer 2 · answered Apr 06 '17 at 12:10

ROWNUM is a bit tricky to use. The ROWNUM pseudocolumn always starts with 1 for the first result that actually gets fetched. If you filter for ROWNUM>10, you will never fetch any result and therefore will not get any.

If you want to use it for paging (not that you really should), it requires nested subqueries:

select * from 
   (select rownum n, x.* from 
     (select * from mytable order by name) x
   )
   where n between 3 and 5;

Note that you need another nested subquery to get the order by right; if you put the order by one level higher

select * from 
   (select rownum n, x.* from mytable x order by name)
   where n between 3 and 5;

it will pick 3 random(*) rows and sort them, but that is ususally not what you want.

(*) not really random, but probably not what you expect.

See http://use-the-index-luke.com/sql/partial-results/window-functions for more effient ways to implement pagination.

http://stackoverflow.com/questions/174595/in-an-oracle-database-what-is-the-difference-between-rownum-and-row-number — Erich Kitzmueller, Apr 07 '17 at 12:16

score 0 · Answer 3 · answered Apr 04 '17 at 21:00

Not sure exactly what you're after based on your question wording, but it seems like you want to see your specialized table of all records with a row number between two values, and in an adjacent field in each record see the total count of records. If so, you can try selecting everything from your table and joining a subquery of a COUNT value as a field by saying where 1=1 (i.e. everywhere) tack that field onto the record. Example:

SELECT *
FROM table_name LEFT JOIN (SELECT COUNT(*) AS NUM_RESULTS FROM table_name) ON 1=1
WHERE ? <= ROWNUM AND ROWNUM < ?

How can I get the total result count, and a given subset ('page' of results) with the same SQL Query with Oracle

3 Answers3