Re: finds gaps in sequential numbers?
- From: Pip''''n <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 15 May 2006 13:04:02 -0700
I too am looking for a way to determine missing numbers in a sequence.
Your solution is a wonderful way to determine the ranges missing. Is there a
way to list each missing number through this solution?
Pip''n
"Tom Ellison" wrote:
Dear Jennifer:.
Step 1: find a Number where the next number is missing, but not the
greatest number in the table:
SELECT Number
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)
Here, a subquery in the EXISTS clause looks for Number + 1.
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1
Notice the use of Aliases T and T1. This allows the query to look at the
whole table using T1 without referenced to the "outer" query based on an
instance of the table called T. The WHERE clause relates the two copies of
the table. It says, "look for the next sequential value in Number, relative
to the value in the outer query which is considering each value of Number
that exists."
Another subquery:
SELECT MAX(Number) FROM YourTable
Simply returns the largest value of Number in the table. We want to omit
that value from our list of missing ranges.
Now we have the values of Number that are just before the range of missing
numbers. Add 1 to define the start of each range. Now, what is the upper
end of each range? To find that, find the next larger Number that does
exist and subtract 1:
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1
That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.
Read up on the subjects "alias" and "subquery".
Tom Ellison
"Jennifer@DakCo" <JenniferDakCo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:52B35248-FDDA-4C7E-84EB-C498470481C6@xxxxxxxxxxxxxxxx
Tom - thanks so much for the info! I spent a lot of time trying to find a
solution for this without much success until you responded to my post.
And yes, I think it would benefit me if you could break this down and
explain how it works.
Jennifer
"Tom Ellison" wrote:
Dear Jennifer:
I would do this with a subquery that checks for N + 1 not existing as the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of
those
greater than).
SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number > T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number <
(SELECT MAX(Number) FROM YourTable)
Substitute the actual name of YourTable and the column name having the
Number in it.
This will work much more quickly if there is an index on the column I
called
Number.
Would you benefit from me breaking this down and explaining how it works?
Tom Ellison
If you post the name of the table and of the column having these numbers,
I
could create the actual SQL.
"Jennifer@DakCo" <Jennifer@DakCo@discussions.microsoft.com> wrote in
message
news:0D212AD0-D563-46B6-AE65-006E2A5A0CC1@xxxxxxxxxxxxxxxx
How do I create a query, in MS Office Access 2000, that will find gaps
in
manually typed sequential numbers?
- Follow-Ups:
- Re: finds gaps in sequential numbers?
- From: Tom Ellison
- Re: finds gaps in sequential numbers?
- Prev by Date: Re: Creating a Delete Query from an Unmatched Query
- Next by Date: Re: finds gaps in sequential numbers?
- Previous by thread: Re: filter by query
- Next by thread: Re: finds gaps in sequential numbers?
- Index(es):
Relevant Pages
|
