CODEWITHSUNDEEP
sqloracle-database
Himanshu Agrawal
Himanshu Agrawal

Reputation: 13

How to find if there are more records in a table using oracle sql

I am using a sql to select data from a table for a particular range of records. I am using rownum to implement greater and less than logic to arrive at the required set of data. With the use of below sql I can fetch the records from 21 to 40 from my table. In total for this condition table contains 100 rows. Through this sql I want to fetch an indicator(value) which tells that there are more records for this condition.

I could not find any solution in google. Sql -

select * from ( select rownum rnum, a.* from(SELECT TO_CHAR(D.DATE,'YYYYMMDD'),D.TYPE, 
TO_CHAR(D.VDATE,'YYYYMMDD'),D.AMT,D.PARTICULAR,D.NUM,D.ID,
D.CODE,D.INFO FROM MySCHEMA
.MYTABLE D WHERE D.DATE >= TO_CHAR(TO_DATE('20160701','YYYYMMDD'),'DD-MON-RRRR') 
AND D.DATE <= TO_CHAR(TO_DATE('20161105','YYYYMMDD'),'DD-MON-RRRR') AND D.XDATE >= TO_CHAR(TO_DATE('20160701','YYYYMMDD'),'DD-MON-RRRR') 
AND D.XDATE <= TO_CHAR(TO_DATE('20161105','YYYYMMDD'),'DD-MON-RRRR') 
AND D.FLG='Y' AND D.TYPE IN('D','C') 
AND
 D.ACI = 'CO6'
 ORDER BY D.DATE  DESC
 )
a where rownum <= 40 ) where  rnum >= 21;

Upvotes: 1

Views: 120

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

You can add a count(*) over () total_rows to your inner select. That will tell you how many rows the inner query would return without the rownum predicates. That is going to mean, however, that every time you ask for a page of results, Oracle has to execute the inner query completely and then discard all the rows you aren't fetching. That's going to be more expensive than what you are currently doing

select * 
  from ( select rownum rnum, a.* 
           from(SELECT count(1) over () total_rows,
                       <<the rest of your query>>

Upvotes: 1

Related Questions