I would like to figure out if I can select in a single query the total number of results of the query if it wasn’t paginated with the ROWNUM values.
My Table is very simple, just these 3 columns (ID, DESCRIPTION, NAME)

  select TOTALRESULTS, ID, DESCRIPTION, NAME
  from ( select count(*) as TOTALRESULTS, ID, DESCRIPTION, NAME, ROWNUM as rnum 
      from TESTDATAITEM where DESCRIPTION = 'test' and NAME = 'foo' 
      order by ID )
  a where rnum between 0 AND 9 order by rnum

This is the query that my java backend runs with myBatis to return the first page (10 results) to the React UI, while instead, the total results number if not limited with "rnum between 0 AND 9" would be hundreds..

Is there a way to obtain the information of the total number of results in the same query or the best way is to run another query without limiting the results between 0 and 9?

I’ve tried to add a count() for TOTALRESULTS and a group by but it’s not working the way I thought

 select TOTALRESULTS, ID, DESCRIPTION, NAME
 from ( select count(*) as TOTALRESULTS, ID, DESCRIPTION, NAME, ROWNUM as rnum 
     from TESTDATAITEM where DESCRIPTION = 'TEST' and NAME = 'TEST_NAME' 
     group by ID, NAME, ROWNUM
     order by ID )
 a where rnum between 0 AND 9 order by rnum

Ps I need to use ROWNUM although it’s ugly (instead of LIMIT /OFFSET) cause there is an old version of oracle

thanks

>Solution :

You can use the analytic version of count, over the entire result set:

select count(*) over () as TOTALRESULTS

giving you (with some dummy sample data):

select TOTALRESULTS, ID, DESCRIPTION, NAME
  from ( select count(*) over () as TOTALRESULTS, ID, DESCRIPTION, NAME, ROWNUM as rnum 
      from TESTDATAITEM where DESCRIPTION = 'test' and NAME = 'foo'
      order by ID )
  a where rnum between 0 AND 9 order by rnum

fiddle