Joe Celko's SQL for Smarties, 3rd Edition

24.2. Numbering Regions

Instead of looking for a region, we want to number the regions in the order in which they appear. For example, given a view or table with a payment history, we want to break it into groupings of behavior—for example, whether or not the payments were on time or late.

CREATE TABLE PaymentHistory
(payment_nbr INTEGER NOT NULL PRIMARY KEY,
 paid_on_time CHAR(1) DEFAULT 'Y' NOT NULL
     CHECK(paid_on_time IN ('Y', 'N')));

INSERT INTO PaymentHistory
VALUES (1006, 'Y'), (1005, 'Y'),
(1004, 'N'),
(1003, 'Y'), (1002, 'Y'), (1001, 'Y'),
(1000, 'N');

The results we want assign a grouping number to each run of ontime/late payments, thus:

Results grping payment_nbr paid_on_time =============================== 1 1006 'Y' 1 1005 'Y' 2 1004 ...