Joe Celko's SQL for Smarties, 3rd Edition

24.5. Run and Sequence Queries

Runs are informally defined as sequences with gaps. That is, we have a set of unique numbers whose order has some meaning, but the numbers are not all consecutive. Time series information in which the samples are taken at irregular intervals is an example of this sort of data. Runs can be constructed in the same manner as the sequences by making a minor change in the search condition. Let’s do these queries with an abstract table made up of a sequence number and a value:

CREATE TABLE Runs (seq_nbr INTEGER NOT NULL PRIMARY KEY, val INTEGER NOT NULL); Runs seq_nbr val =========== 1 6 2 41 3 12 4 51 5 21 6 70 7 79 8 62 9 30 10 31 11 32 12 34 13 35 14 57 15 19 16 84 17 80 18 90 19 63 20 53 21 3 22 59 23 69 24 27 25 ...