Re: Tough SQL Problem

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Wed, 8 Feb 2006 19:55:02 +0100 (CET)
Message-ID: <50370.213.162.65.17.1139424902.bloek@pwebmail.utanet.at>

Hi Ethan,

I assume that there is a seq_id (see post from Mark). You can try something like teh sequence number of a call cluster. The rest is ordinary count with group by.

HTH, Jaromir

create table t1
( seq_id number,
 PROCEDURE_ID number);

insert into t1 values (1,1);
insert into t1 values (2,2);
insert into t1 values (3,1);
insert into t1 values (4,1);
insert into t1 values (5,1);
insert into t1 values (6,1);
insert into t1 values (7,1);
insert into t1 values (8,2);
insert into t1 values (9,1);
insert into t1 values (10,1);
insert into t1 values (11,2);
insert into t1 values (12,1);

commit;

SQL> select PROCEDURE_ID,seq_id,
  2 sum(is_start) over (order by seq_id) call_cluster_sequence   3 from (
  4 select
  5 PROCEDURE_ID,seq_id,
  6 decode(lag(PROCEDURE_ID) over (order by seq_id), PROCEDURE_ID,0,1) is_start
  7 from t1) order by seq_id;

PROCEDURE_ID SEQ_ID CALL_CLUSTER_SEQUENCE

------------ ---------- ---------------------
           1          1                     1
           2          2                     2
           1          3                     3
           1          4                     3
           1          5                     3
           1          6                     3
           1          7                     3
           2          8                     4
           1          9                     5
           1         10                     5
           2         11                     6
           1         12                     7

12 rows selected.

> I have a log table that is logging various PLSQL calls. Assume the table
> looks like this...
>
> PROCEDURE_ID
> =============1
> 2
> 1
> 1
> 1
> 1
> 1
> 2
> 1
> 1
> 2
> 1
>
> Looking at this I can see that the max times 1 was called
> consecutivley is 5. I have no clue how I would do this in SQL without
> writing a function of some sort to keep a count and return the max
> count. Anyone know if this can be done using and built in functions?
>
> - Ethan
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 08 2006 - 12:55:02 CST