J
At 10:24 AM 3/6/2003 -0600, you 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/ > >
