How to Find Consecutive Rows with SQL

View All Scripts Login to Run Script

Script Name How to Find Consecutive Rows with SQL
Description Examples of how you can find rows with consecutive values. This gives two solutions. The Tabibitosan method which uses row_number(). And a pattern matching approach which uses match_recognize in Oracle Database 12c.
Area SQL Analytics
Contributor Chris Saxon (Oracle)
Created Wednesday February 07, 2018

Statement 1

create table running_log ( 
  run_date          date not null,  
  time_in_seconds   int  not null, 
  distance_in_miles int  not null 
)

Table created.

Statement 2

Populate the table with a person's running log. The goal is to group together consecutive days of training.

begin  
  insert into running_log values (date'2018-01-01', 420, 1);  
  insert into running_log values (date'2018-01-02', 2400, 5);  
  insert into running_log values (date'2018-01-03', 2430, 5);  
  insert into running_log values (date'2018-01-06', 2350, 5);  
  insert into running_log values (date'2018-01-07', 410, 1);  
  insert into running_log values (date'2018-01-10', 400, 1);  
  insert into running_log values (date'2018-01-13', 2300, 5);  
  insert into running_log values (date'2018-01-14', 425, 1);  
  insert into running_log values (date'2018-01-15', 422, 1);  
  commit;  
end;

1 row(s) inserted.

Statement 3

select * from running_log


RUN_DATE TIME_IN_SECONDS DISTANCE_IN_MILES
01-JAN-18 420 1
02-JAN-18 2400 5
03-JAN-18 2430 5
06-JAN-18 2350 5
07-JAN-18 410 1
10-JAN-18 400 1
13-JAN-18 2300 5
14-JAN-18 425 1
15-JAN-18 422 1

9 rows selected.

RUN_DATE	TIME_IN_SECONDS	DISTANCE_IN_MILES
01-JAN-18	420	1
02-JAN-18	2400	5
03-JAN-18	2430	5
06-JAN-18	2350	5
07-JAN-18	410	1
10-JAN-18	400	1
13-JAN-18	2300	5
14-JAN-18	425	1
15-JAN-18	422	1

Statement 4

For the Tabibitosan method, assign each row an increasing number, ordered by the run date. When you subtract this from the run_date, consecutive rows have the same result.

Tabibitosan method

select run_date, row_number() over (order by run_date) rn ,  
       run_date - row_number() over (order by run_date) grp_date  
from   running_log


RUN_DATE RN GRP_DATE
01-JAN-18 1 31-DEC-17
02-JAN-18 2 31-DEC-17
03-JAN-18 3 31-DEC-17
06-JAN-18 4 02-JAN-18
07-JAN-18 5 02-JAN-18
10-JAN-18 6 04-JAN-18
13-JAN-18 7 06-JAN-18
14-JAN-18 8 06-JAN-18
15-JAN-18 9 06-JAN-18

9 rows selected.

RUN_DATE	RN	GRP_DATE
01-JAN-18	1	31-DEC-17
02-JAN-18	2	31-DEC-17
03-JAN-18	3	31-DEC-17
06-JAN-18	4	02-JAN-18
07-JAN-18	5	02-JAN-18
10-JAN-18	6	04-JAN-18
13-JAN-18	7	06-JAN-18
14-JAN-18	8	06-JAN-18
15-JAN-18	9	06-JAN-18

Statement 5

You can then group by this calculated value to get summary statistics. For example, how many rows there are in the group, the first and last values and so on.

Tabibitosan method summary

with grps as (  
  select run_date, row_number() over (order by run_date) rn ,  
         run_date - row_number() over (order by run_date) grp_date  
  from   running_log  
)  
  select min(run_date) first_run, max(run_date) last_run,   
         count(*) runs,   
         row_number() over (order by min(run_date)) grp  
  from   grps  
  group  by grp_date  
  order  by min(run_date)


FIRST_RUN LAST_RUN RUNS GRP
01-JAN-18 03-JAN-18 3 1
06-JAN-18 07-JAN-18 2 2
10-JAN-18 10-JAN-18 1 3
13-JAN-18 15-JAN-18 3 4

4 rows selected.

FIRST_RUN	LAST_RUN	RUNS	GRP
01-JAN-18	03-JAN-18	3	1
06-JAN-18	07-JAN-18	2	2
10-JAN-18	10-JAN-18	1	3
13-JAN-18	15-JAN-18	3	4

Statement 6

Starting in 12c, you can use match_recognize to do the same thing. A row is consecutive with the previous when the current date equals the previous date plus one. So define a pattern variable which checks for this. This follows an "always true" initial variable. Classifier and match_number are functions for match_recognize. Classifier shows which variable was matched and match_number returns the group number.

Pattern matching method

select *  
from   running_log  
match_recognize ( 
  order by run_date 
  measures  
    classifier() as var, 
    match_number() as grp 
  all rows per match 
  pattern ( strt consecutive* ) 
  define  
    consecutive as run_date = ( prev ( run_date ) + 1 ) 
)


RUN_DATE VAR GRP TIME_IN_SECONDS DISTANCE_IN_MILES
01-JAN-18 STRT 1 420 1
02-JAN-18 CONSECUTIVE 1 2400 5
03-JAN-18 CONSECUTIVE 1 2430 5
06-JAN-18 STRT 2 2350 5
07-JAN-18 CONSECUTIVE 2 410 1
10-JAN-18 STRT 3 400 1
13-JAN-18 STRT 4 2300 5
14-JAN-18 CONSECUTIVE 4 425 1
15-JAN-18 CONSECUTIVE 4 422 1

9 rows selected.

RUN_DATE	VAR	GRP	TIME_IN_SECONDS	DISTANCE_IN_MILES
01-JAN-18	STRT	1	420	1
02-JAN-18	CONSECUTIVE	1	2400	5
03-JAN-18	CONSECUTIVE	1	2430	5
06-JAN-18	STRT	2	2350	5
07-JAN-18	CONSECUTIVE	2	410	1
10-JAN-18	STRT	3	400	1
13-JAN-18	STRT	4	2300	5
14-JAN-18	CONSECUTIVE	4	425	1
15-JAN-18	CONSECUTIVE	4	422	1

Statement 7

This excludes "all rows per match", so you get the default of "one row per match" i.e. one per group. You can use the measures clause to define columns showing the number of rows, etc. as in the Tabibitosan group by summary.

Pattern matching summary results

select *   
from   running_log   
match_recognize (  
  order by run_date  
  measures   
    first(run_date) as first_run,  
    last(run_date) as last_run,  
    count(*) as runs,  
    match_number() as grp  
  pattern ( strt consecutive* )  
  define   
    consecutive as run_date = ( prev ( run_date ) + 1 )  
)


FIRST_RUN LAST_RUN RUNS GRP
01-JAN-18 03-JAN-18 3 1
06-JAN-18 07-JAN-18 2 2
10-JAN-18 10-JAN-18 1 3
13-JAN-18 15-JAN-18 3 4

4 rows selected.

FIRST_RUN	LAST_RUN	RUNS	GRP
01-JAN-18	03-JAN-18	3	1
06-JAN-18	07-JAN-18	2	2
10-JAN-18	10-JAN-18	1	3
13-JAN-18	15-JAN-18	3	4

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database