Joe Celko's SQL for Smarties, 4th Edition

32

Subsequences, Regions, Runs, Gaps, and Islands

We have already talked about the GROUP BY clause in queries. The groups in a GROUP BY do not depend on any orderings. But you will often want to make other groupings that depend on an ordering of some kind. Examples of this sort of data would be ticket numbers, time series data taken at fixed intervals, and the like, which can have missing data or subsequences that are of interest. These things are easier to explain with examples. Consider a skeleton table with a sequential key and seven rows.

CREATE TABLE List

(list_seq INTEGER NOT NULL PRIMARY KEY,

list_val INTEGER NOT NULL UNIQUE);

INSERT INTO List

VALUES (1, 99), (2, 10), (3, 11), (4, 12), (5, 13), (6, 14), (7, 0);

A subsequence in the list_val ...