That would work, and I appreciate it. Sometimes though, I get all contemplative and romantic about single-query elegance, even if the single-query is complex, its impact subtle but discretely powerful, and my lack of comprehension as great as what it does for me ...
Umm, what was I supposed to be talkin' about, wine, women, song, or SQL? Anyway, I'm also tickled pink because this (if I recall correctly) is the first time I've actually used one of Celko's queries in my work, even though I've had it f/a few years, so, I'm a bit excited about this fact. At some point, I hope to have the time to sit down, study this fm a set-oriented perspective, and come to a fuller understanding thereof - I do not currently aspire to reach a zen-state thereon, although I do indeed harbor that desire ... Later, Steve in Memphis ----- Original Message ----- From: "Scott Salisbury" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, March 06, 2003 11:30 AM Subject: Re: [rStreet] AUTONUM||Equivalent in a View (and about) Finding Missing/Omitted Numbers in a Sequence of Numbers ... > Steve, > > I'd just do a CREATE TEMP TABLE and then I'd stuff it > full of empty rows and slap an AUTONUM on the thing > and then I'd do a sub-select to get what you want. Like > this: > > CREATE TEMP TABLE Junk (JunkInt INT) > You could load Junk with a SELECT from another table > or however you could most quickly load Junk. It doesn't > matter what you load into Junk. > > INSERT INTO Junk (JunkInt) SELECT Id FROM Id_Numbers > INSERT INTO Junk (JunkInt) SELECT Id FROM Id_Numbers > > AUTONUM JunkInt IN Junk USING 1 1 NUM > > SELECT JunkInt FROM Junk WHERE JunkInt NOT IN + > (SELECT Id FROM Id_Numbers) > > This should give you what you want. Maybe there's a > better way to do this sort of thing but this is how I've > always done it. > > Scott > ==== > 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/ > > > > > >>> > >>> > >> > >>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/ > >> > >> > >> > >> > > > > > > > >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/ > > > > > > > > > > > > > > -- > Scott > ==== > Scott J. Salisbury > Matrix Data Systems / The R:Street Journal > 5114 Point Fosdick Drive NW, Suite E-272 > Gig Harbor, WA 98335-1733 > > mailto:[EMAIL PROTECTED] > mailto:[EMAIL PROTECTED] > http://www.MatrixDataSystems.Com > http://www.rStreet.Com > Seattle: 206-448-7975 > Tacoma: 253-444-5166 > Fax: 305-425-0491 > > > > > [Non-text portions of this message have been removed] > > > ------------------------ Yahoo! Groups Sponsor ---------------------~--> > Get 128 Bit SSL Encryption! > http://us.click.yahoo.com/LIgTpC/vN2EAA/xGHJAA/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/ > >
