Mina Mina - 5 months ago 6
SQL Question

SQL query to find the length of the serie of values

I need a query that evaluates the longest uninterrupted series of subsequent "1"'s in the column

FL_SUCC_EXEC
. For the following data in table
TEST(row_no number, fl_succ_exec number(1))
, the result of the query should be "6".

Rows are ordered by
row_no
.

ROW_NO FL_SUCC_EXEC
---------- ------------
1 1
2 1
3 1
4 0
5 1
6 1
7 1
8 1
9 1
10 1
11 0
12 1
13 1
14 1
15 1


I can do this in PL/SQL :

declare
temp_cnt pls_integer default 0;
total_cnt pls_integer default 0;
begin
for rec in (select row_no, fl_succ_exec from test order by row_no)
loop
if temp_cnt > total_cnt
then
total_cnt:=temp_cnt;
end if;

if rec.fl_succ_exec!=0
then
temp_cnt:=temp_cnt+rec.fl_succ_exec;
else
temp_cnt:=0;
end if;

end loop;
dbms_output.put_line(total_cnt);
end;


But I'm still hoping for SQL solution. Is there any?

Answer

Try:

SELECT max( count(*) ) As longest_uninterrupted_series
FROM (
    select fl_succ_exec,
           sum( case when fl_succ_exec = 1 then 0 else 1 end ) 
              over ( order by row_no ) xx 
    from test
)
WHERE fl_succ_exec = 1
GROUP BY xx;