Steve, I love this kind of stuff. Thanks for the sql example.
To get the data you need you might run a cursor down the table. As the cursor finds values that are not next in sequence it can write the missing values to a file. Not as elegant as an sql solution, but you could write the routine in 5 minutes. Ben Petersen On 6 Mar 2003, at 10:24, J. Stephen Wills wrote: > 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/ > > > > >
