Home » SQL & PL/SQL » SQL & PL/SQL » Get count of first 5000 rows from the result set
Get count of first 5000 rows from the result set [message #307122] Mon, 17 March 2008 21:06 Go to next message
Shalini1978
Messages: 7
Registered: March 2008
Junior Member
I have a table that has lots of records(50,000). I need to search specific rows from this table. Then, from this resultset that is returned, I would like to count number of records till first 5000 records i.e. if number of records are more than 5000, I would like to stop at 5000. If number of records in the result set are less than 5000, I would like to stop counting at that point and return count back to the application.


I need to do this to save time incurred in counting 50,000 records because I am concerned with first 5000 only(or less if resultset has less than 5000).
Re: Get count of first 5000 rows from the result set [message #307126 is a reply to message #307122] Mon, 17 March 2008 21:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
WHERE ROWNUM <= 5000
Re: Get count of first 5000 rows from the result set [message #307127 is a reply to message #307126] Mon, 17 March 2008 21:28 Go to previous messageGo to next message
Shalini1978
Messages: 7
Registered: March 2008
Junior Member
I need to get the count if resultset has less than 5000 records.
Re: Get count of first 5000 rows from the result set [message #307138 is a reply to message #307127] Mon, 17 March 2008 23:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
select max (rownum) 
from   (select ...
        from   ... 
        order  by ...) 
where  rownum <= 5000;


SCOTT@orcl_11g> select max (rownum)
  2  from   (select *
  3  	     from   emp
  4  	     order  by empno)
  5  where  rownum <= 5000
  6  /

MAX(ROWNUM)
-----------
         14



Re: Get count of first 5000 rows from the result set [message #307139 is a reply to message #307127] Mon, 17 March 2008 23:35 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You also might find this interesting:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:76812348057
Previous Topic: Can I use a bind arguments in CURSOR for a schema object?
Next Topic: more element in IN clause
Goto Forum:
  


Current Time: Sat Dec 03 20:03:56 CST 2016

Total time taken to generate the page: 0.10624 seconds