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

13.2. Methods of Attack

This is a simple problem, but the replies to it illustrate approaches to solutions in any SQL. The problem is a single-column table that holds the time in seconds from some starting time (time zero or t0) of some event. For simplicity, the event times are captured in even seconds from a time zero.

CREATE TABLE Events
(event_time DECIMAL(4,1) NOT NULL PRIMARY KEY
 CHECK(event_time  =  ((10 * event_time)/ 10))
 -- whole seconds);
INSERT INTO Events
VALUES (500), (505), (510), (535),
       (910), (939), (944), (977);

I need to assign a group number to the above values, based on a time interval of 30 seconds. The numbering does not matter, just as long as the numbering increases with the event times. For example, this is a correct result ...

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