O'Reilly logo

Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL 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

5.1. Sequence Table

The Sequence table is a simple list of integers from 1 to (n) that is used in place of looping constructs in a procedural language. Rather than incrementing a countervalue inside a loop, we try to work in parallel with a complete set of values.

The table can include other data related to sequential numbering, such as the ordinal and cardinal number names, repeating or nonrepeating pseudo-random numbers, prime number flags, or whatever you need for your particular enterprise.

This table has the general declaration:

CREATE TABLE Sequence (seq INTEGER NOT NULL PRIMARY KEY CONSTRAINT non_negative_nbr CHECK (seq > 0), -- cardinal name VARCHAR(25) NOT NULL, -- ordinal_name VARCHAR(25) NOT NULL, ... CONSTRAINT seq_is_complete CHECK ...

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