Highest Value in a field 2

[coldan]

Revising old code.

Some years ago I put this in my app

scan


If TEMP_A.ref_id > NumTop

NumTop = TEMP_A.ref_id
Endif

Endscan

I feel sure there is a better way..can someone point me to it

Thanks

Coldan

 

[audiopro]

Order the table on (ref_id desc) and then take the top value or select MAX.

Keith

http://www.studiosoft.co.uk

 

[Mike Lewis]

Hi Coldan

You could do this:

SELECT MAX(Ref_ID) FROM Temp_A INTO ARRAY laTemp
NumTop = laTemp(1)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[brigmar]

CALCULATE MAX(ref_id) to NumTop IN yourtable

If you're using this to generate the next 'ID' in the table, be mindful to include DELETED records (SET DELETED OFF) so that you won't use one that has been used and deleted.

 

[Stella740pl]

coldan,

I ran the following time test several times, using tables with over 10 million records each
(located localy, so the network traffic wouldn't affect results), each of them properly indexed on most fields.

a=seconds()

NumTop=0
scan
   If count>NumTop
      NumTop=count
   Endif
Endscan

b=seconds() 

NumTop=0
scan
   NumTop=iif(count>NumTop, count, NumTop)
Endscan
 
c=seconds()  

SELECT MAX(count) FROM MyTable INTO ARRAY laTemp
NumTop=laTemp(1)
 
d=seconds() 

CALCULATE MAX(count) to NumTop
 
e=seconds()

?b-a
?c-b
?d-c
?e-d

While all methods took different number of seconds each time, the all were consistently several times slower than Mike's suggestion of using SELECT MAX(...).
It was blazingly fast, and took only less than 0.035 seconds, in some runs actually rounding to 0.000.
It's a clear winner.

 

[cbasoz]

While select max(ref_id) from myTable would be very fast it wouldn't be as fast as audiopro's suggestion of ordering descending (assuming you had an index on ref_id) and taking the first one. Also select max() from ... wouldn't select the max value if it is buffered and you need to take data in buffer into account. Audiopro's suggestion is nice IMHO.

set order to tag ref_id desc
locate
luMax = ref_id

Also you should be carefull with select max() ... if you are using VFP9 data engine. You need to know its behavior in VFP8 and later versus your need in your application:

If you need to an 'empty' value when there are no records, you don't need to do anything special with Audiopro's suggestion. With "select max() from ..." however you would get .Null. as a result. You could easily workaround it by checking if laTemp[1] is null:

numTop=nvl(laTemp,0)

Cetin Basoz
MS Foxpro MVP, MCP

 

[Stella740pl]

Cetin,

While select max(ref_id) from myTable would be very fast it wouldn't be as fast as audiopro's suggestion of ordering descending (assuming you had an index on ref_id) and taking the first one.

While I like Audiopro's suggestion (and somehow missed it the first time I read this thread), it wasn't noticeably faster for me (maybe an occasional 1/1000 of a second after half a dozen tests, with an occasional lead being on the other side) on about 10.5 million records tables.

As a matter of fact, whichever method of the two was used on the newly open table for the first time, took longer, from about 0.025 to 0.051 seconds, with the second one being close to 0.000. All consecutive test finished with nearly the same results.

For that, I already have a descending index on the field in question, so FoxPro itself might have used it to optimize the SELECT.

I am using VFP6, though.

 

[alan92rttt]

Is their is an index FoxPro will use it to speed up the query.

When speed testing any runs after the first may be faster because things that VFP needed the 1st time are already in memory.