Re: Find Missing Numbers in a Sequence
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Thu, 20 Dec 2007 08:31:22 -0500
To list the start of the gap you can use
SELECT A.ID+1 as GapStart
FROM YourTable as A LEFT JOIN YourTable As B
ON A.ID + 1= B.ID
WHERE B.ID is Null
AND A.ID Between 25 and 95
This will list the start of the gap.
For instance if you were checking 25 to 30 and the records were 25,28,30,31
this should return 26 and 29. Note that it doesn't detect that 27 is also
missing. The simplest solution to catch every item that is missing would be
to use an auxiliary table that contains nothing but a sequence of numbers
from 1 to the highest number you expect to see. With that the query becomes
SELECT S.TheNumber
FROM SequenceTable as S LEFT JOIN YourTable
ON S.TheNumber = YourTable.ID
WHERE S.TheNumber Between 25 and 95
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"KARL DEWEY" <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:555106A4-74CD-4000-A4FF-AF0572E07B6E@xxxxxxxxxxxxxxxx
This finds the first missing but sequence must start at 1 --
SELECT TOP 1 [ID]-1 AS Missing
FROM Your_Table AS Q
WHERE ((((SELECT COUNT(*) FROM Your_Table Q1
WHERE Q1.ID < Q.ID)+1)<>[ID]))
ORDER BY [ID]-1;
--
KARL DEWEY
Build a little - Test a little
"Gary Dolliver" wrote:
Hi everyone,
I have a table that is imported to daily, and sometimes orders are not
correctly migrated into the system. As all numbers that come in are in
numerical order, is there a way to create a query that will return a
result
set of numbers from the sequence that are missing, based on the highest
and
lowest value of the number sequence?
Help is always appreciated, thanks!
-gary
.
- Prev by Date: Re: Can an update query be used to update/produce a query?
- Next by Date: Re: Using Code to build criteria
- Previous by thread: Re: Find Missing Numbers in a Sequence
- Next by thread: Grouping and counting
- Index(es):
Relevant Pages
|
