Create an index on the @ID .... makes it easy/faster to find :-) Ross Ferris Stamina Software Visage > Better by Design!
-----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charles Stevenson Sent: Friday, 27 May 2011 11:33 PM To: U2 Users List Subject: Re: [U2] [UV] Emulate SQL MAX() function at TCL I am going to assume we are talking about a HUGE file, otherwise efficiency wouldn't much matter. I don't know a GOOD way outside basic or sql to prevent a sort-selected list from being created. For humongous files, that may be painful. Here is a way, not a GOOD way, just a way. I just can't help myself: 1. Use an I-descriptor to compare last max to current id and set new max if necessary. 2. Use REFORMAT to continually overwrite the same record with the newly found max. ("737" is the biggest id in this example): >CLEAR.FILE TINYWORK File "TINYWORK" has been cleared. >REFORMAT BIGDATA EVAL "'BIGDATA.MAXID'" EVAL "@2; IF @1 > @ID THEN @1 ELSE @ID" BIGDATA.MAXID @2 ; IF @1 > @ID THEN @1 ELSE @ID File Name = TINYWORK >CT TINYWORK BIGDATA.MAXID BIGDATA.MAXID 0001 737 > >DISPLAY -- or if you prefer <<F(TINYWORK,BIGDATA.MAXID,1)>> -- or if you prefer 737 > QED (That's Latin for "Quirky Example Demonstrated") A related I-desc could be used as REFORMAT selection criteria to limit the writes to only when the max changes. On average, that would halve the writes. A huge sort-select is going to be writing to uvtemp anyway, but I'd bet your money it will be faster than the above. __________ If you're going to do it inside basic, like some others have suggested, you might as well try this to avoid creating a sorted select list in memory / uvtemp: SELECT FVAR MAXID = "" LOOP WHILE READNEXT ID IF ID > MAXID THEN MAXID = ID REPEAT But test to see if it is a good idea. I'm continually surprised by RetrieVe's efficiency compared to what I can do in Basic. On 5/27/2011 5:40 AM, Wols Lists wrote: > On 27/05/11 07:03, Boydell, Stuart wrote: >> Just wondering if anyone has a neat trick for picking up the highest ID from >> a file. Where the file ID is an integer. >> >> With UVSQL I would SELECT MAX(@ID) FROM TABLE; - however, the program I need >> to do this within has the file (table) open and if I try to execute it >> returns the error 'Non-SQL re-entrant query calls are not allowed.' >> >> The best I have so far is: >> st = 'SELECT FILE @ID BY.DSND @ID' >> st<-1> = 'LIST ONLY FILE SAMPLE 1' >> exec st capturing cap >> maxId = trim(cap<4>) >> >> Any other ideas? >> > Extending your idea ... > > SELECT FILE @ID BY.DSND @ID SAMPLE 1 > READLIST > > Not quite sure whether SAMPLE is the correct keyword, because I think > one keyword samples then sorts (that might be SAMPLING), but I'm sure > there is one that sorts then samples. > > If you can't find the keyword, the other trick could be > > SELECT FILE @ID BY.DSND @ID > READNEXT > CLEARLIST _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
