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