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

This will work:

SELECT SUBSTR(stuf,7) name, 100000-SUBSTR(stuf,1,6) count FROM( SELECT DISTINCT LPAD(100000-count,6,'0')||name stuf FROM( SELECT count(*) count, name
  FROM mytable
 GROUP BY name))
 WHERE rownum <= 5
/

Now... This uses a quirk of Oracle so it's not necessarily going to always work. What happens is that the "distinct" will cause the rows to be ordered ascending (that's why the 1000000 - count is used).

Anyway, is it possible to write this in SQL? You be the judge.

• Alton

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.
Received on Fri Jun 04 1999 - 13:39:49 CDT