Okay, here's a better description, in detail but also in brief.
Let's say that I have a table, `ID_NUMBERS`, w/1 field, `ID`, w/the
following data :
ID
---------
1
2
4
5
6
8
9
10
13
14
15
Now, what I want is to have a query tell me that fm this "array", I am
missing 3, 7, 11 & 12.
The query I took fm Celko's book goes as follows :
SELECT
t1.ID, MIN(t2.ID)
FROM
ID_NUMBERS t1, ID_NUMBERS t2
WHERE
t1.ID <= t2.ID
AND NOT EXISTS (SELECT *
FROM ID_NUMBERS t3
WHERE t3.ID NOT BETWEEN t1.ID
AND t2.ID
AND ((t3.ID = t1.ID - 1)
OR
(t3.ID = t2.ID + 1)))
GROUP BY
t1.ID
It produces the following result set :
t1.ID MIN(t2.ID)
--------- ---------
1 2
4 6
8 10
13 15
Now, it doesn't give me the omissions, but it does give me the inclusions,
in the form contiguous sequences w/in the overall series of ID's. This is
satisfactory f/now, given that these are the exception(s) to the rule,
albeit these exceptions are what I need to know. The "gaps" in the results
produced are the omissions, again, 3, 7, 11 & 15.
Hey, try it, you'll like it!
Later,
Steve in Memphis
P.S. - My data-field was INT and it was sorted before I ran the query. I
haven't tried anything w/un-sorted data - although the implicit sort in the
GROUP BY might handle this - nor have I tried anything w/other data types,
esp. TEXT. So, short of doing these trials, take this postscript as a
potential caveat. -S.i.M.
----- Original Message -----
From: "J. Stephen Wills" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: "Hari Kusumba" <[EMAIL PROTECTED]>; "Bala Pamarti"
<[EMAIL PROTECTED]>; "Minoo Shinde" <[EMAIL PROTECTED]>
Sent: Wednesday, March 05, 2003 1:30 PM
Subject: Re: [rStreet] AUTONUM||Equivalent in a View (and about) Finding
Missing/Omitted Numbers in a Sequence of Numbers ...
> Having now executed my due diligence, creating a 1-col view and simple
mod's
> to Celko's example have given what I needed, if not all I wanted. I think
I
> can play w/it when I'm able and find a way to have it produce only the
> "omissions". What it does now is show me the "inclusions", so I have to
> fill in the gaps, as it were.
>
> Anybody interested in seeing the details?
>
> Lemme' know,
> Steve in Memphis
>
> ----- Original Message -----
> From: "J. Stephen Wills" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Cc: "Hari Kusumba" <[EMAIL PROTECTED]>; "Bala Pamarti"
> <[EMAIL PROTECTED]>; "Minoo Shinde" <[EMAIL PROTECTED]>
> Sent: Wednesday, March 05, 2003 12:59 PM
> Subject: [rStreet] AUTONUM||Equivalent in a View (and about) Finding
> Missing/Omitted Numbers in a Sequence of Numbers ...
>
>
> > Okay, I don't own a theater, to borrow fm Celko's examples in"SQL for
> Smarties", but I certainly have a similar need to examine a series of
> numbers, a sequence, or actually a set of sequences, as there can be 1:N
> numbers missing - these can be "missing" at any point in the recordset and
> may or may not be contiguous.
> >
> > To use today's example. I have a sample of 340 records, originally
> numbered (ID'd) 1:340. Now, due to processing (a 3rd party system that has
> its own way of doing things) some of the records will have their ID
changed.
> F/example, these 340 records now have MAX(ID)=348. So, by "eyeballing" in
> the Data Browser, I found that ID's 19,20,58,199,209,298,300,326
> [COUNT(listmembers)=8] have been changed to 341:348. (The numbers are
only
> relevant to illustrate the example, which, is "real world", as of 11:42am
> CST, today.)
> >
> > My counts all "balance", so I have all the records I'm s'posed to have,
> but I'd like to execute a query against the ID's I actually receive
> post-processing versus what I had pre-processing. I'd hoped that Celko's
> examples would be close - and they may be, but I haven't done complete due
> diligence yet - but I thought I'd ask y'all about this too.
> >
> > In short, I'm trying to describe via SQL the discontinuities in a
> data-set. That is, have the query results show me, using today's example
the
> 8 ID's that are "missing"||"skipped" fm the set of ID's.
> >
> > Any ideas? If not, any interest in finding out what I do, if I meet
> success?
> >
> > Thanks & Later,
> > Steve in Memphis
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > The rStreet List is dedicated to database application development,
> > R:BASE, PHP, Visual Basic etc...
> >
> > All products mentioned on this list are the trademarks of their
> resepective companies.
> >
> > Post: [EMAIL PROTECTED]
> > Unsubscribe: [EMAIL PROTECTED]
> > http://groups.yahoo.com/group/rStreet/
> >
> > Scott Salisbury - [EMAIL PROTECTED]
> >
> > Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
> >
> >
>
>
> ------------------------ Yahoo! Groups Sponsor ---------------------~-->
> New Yahoo! Mail Plus. More flexibility. More control. More power.
> Get POP access, more storage, more filters, and more.
> http://us.click.yahoo.com/Hcb0iA/P.iFAA/46VHAA/PhFolB/TM
> ---------------------------------------------------------------------~->
>
> The rStreet List is dedicated to database application development,
> R:BASE, PHP, Visual Basic etc...
>
> All products mentioned on this list are the trademarks of their
resepective companies.
>
> Post: [EMAIL PROTECTED]
> Unsubscribe: [EMAIL PROTECTED]
> http://groups.yahoo.com/group/rStreet/
>
> Scott Salisbury - [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
