PL/SQL Limit number of rows returned by query

Allen Pitts
Allen Pitts used Ask the Experts™
on
Hello expert,

Need to discover Proctocol_ID records in the History table that have a lot of records and look at deleting them.
Wrote query copied herewith below. Does the job but it returns 950k rows. Examining the data it seen that if the the records that have count  > 20 or RowNum < 4000 a more manageable number, say 4k
would be returned. Tried limiting it  by count and Rownum, as shown in the commented lines in query
but no joy, probably because the group by clause does not allow a where limiter.

How can I get  the group by to work but limit the number of rows to thousands instead of hundreds of thousands?

Thanks

Allen in Dallas

    with CTE as (
 select hpr.protocol_id from CCM.H_Protocol hpr
 join CCM_protocol pro on pro.protocol_id =  hpr.protocol_id
 where pro.is_disabled = 0)
 select protocol_id, count(*) as Num
 from CTE
--where rownum <4000
--where Num > 20
  group by protocol_id
--where rownum <4000  
--where Num > 20
  order by Num desc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent OlsenData Warehouse / Database Architect

Commented:
Hi Allen,

The quick and easy way is to sort the rows descending in the CTE and then use rownum to limit the number of rows.

You can also use the inline analytics functions instead of sorting them.


Kent
Database Administrator / Software Engineer
Commented:
Hi,

You could limit the results by using having clause like this (assuming that you are on 12c)

with CTE as (
 select hpr.protocol_id from CCM.H_Protocol hpr
 join CCM_protocol pro on pro.protocol_id =  hpr.protocol_id
 where pro.is_disabled = 0)
 select protocol_id, count(*) as Num
 from CTE
 group by protocol_id
 having count(*) > 20 
 order by count(*) desc 
 FETCH FIRST 4000 ROWS ONLY;

Open in new window


Regards,
   Tomas Helgi
awking00Information Technology Specialist

Commented:
To get 4,000 at a time -
with CTE as
(select hpr.protocol_id as id, row_number() over (order by hpr.protocol_id) as rn
 from CCM.H_Protocol hpr
 join CCM_protocol pro
 on pro.protocol_id =  hpr.protocol_id
 where pro.is_disabled = 0)
select id
from cte
where rn between 1 and 4000
--where rn between 4001 and 8000
--where rn between 8001 and 12000
-- etc.

Of course you can change the start and end rn to get any number of records.
Allen PittsBusiness analyst

Author

Commented:
Thanks Tomas and awking00

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial