Re: Analytics or not

Here's what i came up with:

create table sample (num number, part varchar2(30));

SQL> select * from sample;

       NUM PART

---------- ------------------------------
         1 a
         2 a
         3 b
         4 b
         5 c
         6 c
         7 c
         8 d
         9 d
        10 d
        11 d
        12 e
        13 e

13 rows selected

Executed in 0.271 seconds

SQL> SQL>
select ttt.*
from (
  select tt.*, max(cnt_t) over (partition by part) as mx_p   from (
    select t.*, count(*) over (order by num) as cnt_t from sample t order by num
  ) tt
) ttt
where mx_p <= 10;

       NUM PART                                CNT_T       MX_P
---------- ------------------------------ ---------- ----------
         1 a                                       1          2
         2 a                                       2          2
         3 b                                       3          4
         4 b                                       4          4
         5 c                                       5          7
         6 c                                       6          7
         7 c                                       7          7

7 rows selected

Executed in 0.24 seconds

SQL>
select ttt.*
from (
  select tt.*, max(cnt_t) over (partition by part) as mx_p   from (
    select t.*, count(*) over (order by num) as cnt_t from sample t order by num
  ) tt
) ttt
where mx_p <= 11;

       NUM PART                                CNT_T       MX_P
---------- ------------------------------ ---------- ----------
         1 a                                       1          2
         2 a                                       2          2
         3 b                                       3          4
         4 b                                       4          4
         5 c                                       5          7
         6 c                                       6          7
         7 c                                       7          7
         8 d                                       8         11
         9 d                                       9         11
        10 d                                      10         11
        11 d                                      11         11

11 rows selected

Executed in 0.26 seconds

SQL> Inner most query produces a running total. Middle query produces the "max total reached per group" Outer most filters out.

Does this solve it ?

On Apr 8, 2005 12:02 PM, Martic Zoran <zoran_martic_at_yahoo.com> wrote:
>
> You have the result set from some SQL.
>
> You want to return up to 1000 rows but broken down to
> the last partition in the set partitioned by a few
> logical columns (like employee id, ...).
>
> How to do this?
>
> e.g
>
> you have
>
> 1
> 2
> 2
> 3
> 3
> 3
> 4
> 4
> 4
> 5
> 5
> 5
> 5
>
> but you want back:
>
> 1
> 2
> 2
> 3
> 3
> 3
> 4
> 4
> 4
>
> because 5's are the new partition that will not fit in
> 10 you want to return back.
> Now just replace numbers with the many columns.
>
> Regards,
> Zoran
>
>
> __________________________________
> Yahoo! Messenger
> Show us what our next emoticon should look like. Join the fun.
> http://www.advision.webevents.yahoo.com/emoticontest
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
http://www.freelists.org/webpage/oracle-l