Working SQL: Consecutive Values

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Dec-2001 >

Working SQL: Consecutive Values


Peter Vogel


Sometimes it’s not enough to know that the data is there.

Sometimes you have to know what the data’s neighbors are.

Peter Vogel looks at SQL solutions for finding consecutive

values in your data while providing a strategy for finding

SQL-based solutions.


ORDER isn’t something that’s part of the relational

database theory. If you issue an SQL query and

get the results back in the order A, B, C, and then

you issue the same query but get the results back C, B, A,

nothing has gone wrong. Order is guaranteed only if you

include an Order By clause in your SQL statement. Even

then, if you have two records with the same value in the

field that you’re using to order your data, you can’t

guarantee the order in which they appear. Most database

engines (like Jet) hide this fact by supplying a default

order (typically, the order of the primary key).

Sometimes, though, order matters. If you want to

book a block of seats in a theatre, you need to know which

seats are beside each other. Other problems with the same

structure are finding a run of unused invoices in a series

or a set of empty bins in a warehouse. When working

with dates, you often want to get a result that represents a

consecutive set of days. All of these problems represent

working with sequences of records.

In this article, I’m going to discuss answering the

two typical questions around working with sequences.

But I’m also going to demonstrate a general procedure for

working with SQL. When working with SQL, I almost

always begin by generating a list of all possible answers. I

then eliminate every item in the possible answer list that

isn’t right to leave the real answer. You’ll find this a useful

way to think about SQL problems.


Read more here

Working SQL: Consecutive Values