Re: Q: How to count & put a limit to the output at the same time ??

From: <shiling_at_math.wayne.edu>
Date: Fri, 04 Jun 1999 13:19:34 GMT
Message-ID: <7j8jor$cjl$1@nnrp1.deja.com>

You can have the results to be printed on screen or insert into other table.

declare
v_name t1.name%type;
v_cnt number;
i positive :=1;
limit_num number :=3;
cursor get_first_3rows is

    select name, count(name) cnt

     from t1
     group by name
     order by  cnt desc;

begin

   open get_first_3rows;
   loop

     exit when i>limit_num;
     fetch get_first_3rows
      into v_name,v_cnt;
/*    insert into temp
      values (v_name,v_cnt);  */
     i :=i+1;

   end loop;
   close get_first_3rows;
/* commit; */
end;
/

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
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 04 1999 - 08:19:34 CDT