O'Reilly logo

Joe Celko's SQL for Smarties, 4th Edition by Joe Celko

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required