Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Q: How to count & put a limit to the output at the same time ??
In article <7j8a7g$9jk$1_at_nnrp1.deja.com>,
monsri_at_my-deja.com wrote:
> Hello here,
>
> I have a question (for gurus ?):
> I'd like to perform a COUNT(*), but with a max. of rows returned
> that I can choose (this max. of rows must contain the N first max.
> counts). For instance, instead of getting (unsorted):
> NAME COUNT(*)
> ---- --------
> p_15 8
> n_2 20
> n_3 19
> n_1 20
> p_24 11
> n_4 15
> p_28 10
> n_6 12
>
> if I only need the 5 first max. COUNT(*), I'll get:
> NAME COUNT(*)
> ---- --------
> n_1 20
> n_2 20
> n_3 19
> n_4 15
> n_6 12
>
> How to tell Oracle to stop as soon as I have my 5 rows ?
> I don't think it's possible in SQL, so did anybody write
> anything similar in PL/SQL please ?!?
>
> Thanks a lot for any enlightement !
>
> Regards,
> Seb
Why do you say it cannot be done in SQL? You can drop and create a table with the counts, then select the first 5 from that. If you are going to run this query frequently (at least more frequently than the base tables get updated) then you can keep the table around and update it via triggers.
Basically you are trading storage for easier access and performance.
--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Fri Jun 04 1999 - 07:41:54 CDT