× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.