Re: Analytics or not
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: zoran_martic@xxxxxxxxx
- Date: Fri, 8 Apr 2005 13:09:33 -0400
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@xxxxxxxxx> 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
> --
> //www.freelists.org/webpage/oracle-l
>
--
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
//www.freelists.org/webpage/oracle-l
Other related posts:
