RE: Diffrence between rows in sql

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Tomasz Skorza
>
> I need some sql query statement to find "whole" between numbering in one
> field in rows
> example
> we have a file with filed ID which grow increase 1 for each record
> 10011
> 10012
> 10013
> 10014
> 10017
>
> I i need query which find that number 10015, 10016 is ommited in
> file/records.
>
> have you any idea?

Well, the first is to JOIN the file against itself and look for missing
records.  Soemthing like:

SELECT A.SEQ FROM MYFILE A
  WHERE NOT EXISTS
    (SELECT 1 FROM MYFILE B WHERE B.SEQ = (A.SEQ+1))

This would give you a list of sequence numbers that don't have an
immediately following number.  This will, of course, include the last
sequence number in the file.

If you want "holes" (that is, the number of blank spots), you would have to
then add another field to the select with another join, along the lines of
MIN(C.SEQ) where C.SEQ > A.SEQ.

I don't know if this will work, haven't tried it, but it looks reasonable:

SELECT A.SEQ, MIN(C.SEQ) FROM MYFILE A, MYFILE C
  WHERE NOT EXISTS
    (SELECT 1 FROM MYFILE B WHERE B.SEQ = (A.SEQ+1))
  AND C.SEQ > A.SEQ GROUP BY A.SEQ

This one has the additional side effect of not including the last record in
the file.

Joe