Re: finds gaps in sequential numbers?
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Mon, 15 May 2006 15:12:48 -0500
Dear Pip:
Showing all the missing numbers would be quite a different problem. You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS
can be made made against this list, limited by the largest value, if that's
what you desire.
Given full details, I could work out a solution on this basis, too.
Tom Ellison
"Pip''''n" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4B40ACAA-F79D-447D-998D-62C89036AD3D@xxxxxxxxxxxxxxxx
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: Pip''''n
- Re: finds gaps in sequential numbers?
- References:
- Re: finds gaps in sequential numbers?
- From: Pip''''n
- Re: finds gaps in sequential numbers?
- Prev by Date: Re: finds gaps in sequential numbers?
- Next by Date: Re: Duplicate Records from Append Query
- Previous by thread: Re: finds gaps in sequential numbers?
- Next by thread: Re: finds gaps in sequential numbers?
- Index(es):
Relevant Pages
|
Loading
