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
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
