Oracle - getting the number of consecutive column values within time range

Question

Consider a table with columns date and status (for which the possible values are A and B). After ordering that table, I would like to get the number of most consecutive values for status for value A.

For example, the ordered by date result set below

A
B
B
A*
A*
A*
B
B

would return 3 (marked with an asterisk).

With my very limited knowledge of SQL I have absolutely no idea how to achieve this. I suspect it has something to do with partitions and grouping, but that is as far as I got. Of course, I want to avoid the solution with select * and then post-processing the result set unless it for some reason yet to be discovered proves to be the best.

Test table:

CREATE TABLE TEST
(
  DATE    DATE,
  STATUS  VARCHAR2(1)
)
RESULT_CACHE (MODE DEFAULT)
STORAGE    (
        BUFFER_POOL      DEFAULT
        FLASH_CACHE      DEFAULT
        CELL_FLASH_CACHE DEFAULT
       )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

Insert statements:

Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('06/08/2017 17:16:16', 'MM/DD/YYYY HH24:MI:SS'), 'B');
Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('04/24/2016 21:26:07', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('04/24/2016 21:26:22', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('04/24/2016 21:26:33', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('04/24/2016 21:26:40', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('04/24/2016 21:27:03', 'MM/DD/YYYY HH24:MI:SS'), 'A');
Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('04/24/2016 21:26:13', 'MM/DD/YYYY HH24:MI:SS'), 'B');
Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('04/24/2016 21:26:15', 'MM/DD/YYYY HH24:MI:SS'), 'B');
Insert into TEST
(DATE, STATUS)
 Values
(TO_DATE('04/24/2016 21:26:16', 'MM/DD/YYYY HH24:MI:SS'), 'B');

Thank you for your help! :)

Answer 1

NOTE: Edited from the original answers - I missed the fact that the OP wants the longest string of A'a, not the longest string with the same status. END NOTE

This is a perfect application of the Tabibitosan method (use fixed differences of ROW_NUMBER() values over the same ordering, with and without partitioning by STATUS to create the additional grouping we need). NOTE: I changed the column name from DATE to DT; DATE is an Oracle keyword which shouldn't be used as a column name.

select   max(count(*)) as max_count
from     (
           select status, 
                  row_number() over (order by dt) 
                        - row_number() over (partition by status order by dt) as grp
           from   test
         )
where    status = 'A'
group by status, grp
;

In Oracle 12.1 and above, you can do the same with the new match_recoginze clause:

select max(cnt) as max_count
from   test
match_recognize(
  order by dt
  measures count(*) as cnt
  pattern  ( x+ )
  define   x as status = 'A'
)
;

Answer 2

You need a column that specifies the ordering, because SQL tables represent unordered sets.

You can identify the groups in different ways. In your case, perhaps the simplest way is to count the number of "B"s before each "A" -- that is constant for a sequence of "A"s. Then use another window function to determine the number in the group:

select t.*
from (select t.*, max(cnt) over () as max_cnt
      from (select t.*, count(*) over (partition by status, grp) as cnt
            from (select t.*,
                         sum(case when status <> 'A' then 1 else 0 end) over (order by ?) as grp
                  from t
                  ) t
            where status = 'A'
           ) t
      ) t
where cnt = max_cnt;

If you just want the value "3", then you can use:

select max(cnt)
from (select t.*, count(*) over (partition by status, grp) as cnt
      from (select t.*,
                   sum(case when status <> 'A' then 1 else 0 end) over (order by ?) as grp
            from t
            ) t
      where status = 'A'
     ) t