Re: Query to find amissing number

In article <e969ls$f0n$1@xxxxxxxxxxxxxxxxx>,
John Brock <jbrock@xxxxxxxxx> wrote:
In article <e957qq$8u$1@xxxxxxxxxxxxxxxxxxxxxxxxx>,
Mahesh BS <mahesh.bs@xxxxxxxxxxxx> wrote:

Hello,

I need to write a query to find out a set of missing number in a given
sequence.

Eg : a Column in some table has the following data

Col1

1
2
3
4
5
6
8
9
10

Here I need to write a query to find out that number 7 is missing in the
given sequence.

One possible solution is by using any loop. But I am looking out if the same
can be achieved using any query.

This should work:

select a.Col1 from MyTable a
where not exists
(select b.Col1 from MyTable b where a.Col1 - 1 = b.Col1)
go

Actually the above doesn't return the full list of numbers that
you want, it only alerts you to the location of the gaps in your
sequence, giving you a quick way to determine if any gaps exist.
(Quick if Col1 is indexed that is -- otherwise not so quick).

However by calling a related query (note "+" rather than "-"):

select a.Col1 from MyTable a
where not exists
(select b.Col1 from MyTable b where a.Col1 + 1 = b.Col1)
go

you can get enough information to figure out how big the gaps are,
and therefore what numbers the gaps contain. You are still going
to need a loop to examine the results of the two queries, but if
the number of rows involved is large and the number of gaps is
small this might (perhaps!) be more efficient than looping through
the entire table.

I don't think there is any way to just return the list of missing
numbers directly from a query. SQL queries work by operating on
a set of input rows to produce a set of output rows. Since the
number of missing rows in a sequence is completely arbitrary, I
don't how SQL could accomplish the necessary transformation from
input to output (although SQL has features I've never used, and
maybe one of them can do this).
--
John Brock
jbrock@xxxxxxxxx

.

Relevant Pages

  • RE: Adding numbers to an incomplete numerical list
    ... If your sequence is in A1:A11000 ... beginning with 1, but containing gaps ... The missing numbers will appear at the top of the list....copy them to the ... "hana" wrote: ...
    (microsoft.public.excel.misc)
  • Re: serial numbers how to find gaps
    ... This assumes the start of the sequence is listed. ... That formula automatically starts listing values, ... missing value after the smallest value in the list and continuing listing ... How can I find gaps in a column of serial numbers, ...
    (microsoft.public.excel.newusers)
  • RE: Find missing Working Days
    ... contains one field (intNumbers) and the values of 0 through 9. ... I then create a query that generates numbers from zero through ... Can anyone think of a query that can check if any days are missing? ... results into a table if a number from 1 to 5 is out of sequence but I wonder ...
    (microsoft.public.access.queries)
  • Re: find missing numbers
    ... This will report the first missing number of each sequence. ... similar subquery to find the high end of each missing sequence. ... For a complete query in my response, provide your table name and column ...
    (microsoft.public.access.queries)
  • Re: finds gaps in sequential numbers?
    ... I too am looking for a way to determine missing numbers in a sequence. ... "Tom Ellison" wrote: ... This allows the query to look at the ...
    (microsoft.public.access.queries)