Select the largest number in a table

[steve4king]

Simple question this time I think..
How can I grab the largest or smallest number within a record to input into a calculation?

small1 = (smallest from invoice_no) from invoicefile1
custcount = (count records from customerfile)
use invoicefile2
repl all invoice with (invoice_no -((small1) - (custcount)+500))

 

[JRB-Bldr]

SELECT MAX(Invoice_No) AS MAX,;
  MIN(Invoice_No) AS Min;
  From invoicefile1;
  WHERE !EMPTY(Invoice_no);
  INTO CURSOR Results
SELECT Results
mnMax = Results.Max
mnMin = Results.Min

SELECT Customer
mnCustCount = RECCOUNT()

SELECT InvoiceFile2
<Do Whatever>

Is something like that what you are after?

Good Luck,
JRB-Bldr

 

[brigmar]

Answering the question asked:

CALCULATE MIN(fieldname) TO variable IN table
CALCULATE MAX(fieldname) TO variable IN table

But as Mike says, if you are trying to generate a new unique number (as a tables primary key) it is probably better to keep a record elsewhere in a separate table, OR to define the invoice number field AUTOINC in the table at creation time MSDN article on AutoInc in VFP8.0.

 

[Mike Lewis]

A point to keep in mind:

Using CALUCLATE (as per Brigmar) causes the record pointer to move. Using SELECT (as per JRB-Bldr) doesn't.

It might not matter, but it's worth keeping in mind.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[steve4king]

JRB-Bldr, I think that is exactly what I am looking for.
What I am actually trying to accomplish overall is creating a year end process so that I can archive old transactions and re-number the invoice numbers. (There is a limit of 1mil invoices to prevent 2gb object issue)

So I just need to find out what the minimum invoice number in the remaining file, or the maximum number of the purged/archived data; subtract the number of customers. (will create one invoice per customer to represent past credit/balance.) Then reduce all invoice numbers by that amount. I've already got this process 95% written up, but have not plugged in the (min/max - customer_count).

If you don't mind, I will upload the entire script here on Monday. Then perhaps I might get some suggestions for improving the process/eliminating bugs.

 

[lrice]

Steve,
If I had a file (table) in which a field was named invoice_number

and I wanted to find the lowest and highest of those invoice numbers

then I would copy that field to another file,
or append from

then I would index the new file on that field

Could verify results with a quick browse 1st and last rec
to prove it was done right
then just take the invoice_number of 1st record
and invoice_number of last record.

Or am I missing something ?

 

[mikeopolo]

Is your finance department happy with changing prior invoice numbers (you'll probably tell me they asked you to do it this way!)

 

[steve4king]

Your right Irice, to find the min max I could just:

sort on invoice_no to temp
use temp
messagebox(invoice_no)
**or 
use temp
goto bott
messagebox(invoice_no)

But that would take a while to run, much longer than JRB-BLDR's suggestion.

After looking over my script and considering everything I have learned in the past couple of weeks, I would be a little embarassed to paste my script here. I'm going to refine it first heh.

 

[Mike Lewis]

Steve,

The SORT command has been in the language for 28 years, but I have never ever heard of anyone using it (until today).

The general advice is not to use SORT, but to use INDEX instead. It is many times faster and much less demanding on disk space.

(Sorry if you already knew that.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[steve4king]

I've used sort quite a bit over the last two years.. primarily because I didn't know how to make index work..

 

[Mike Lewis]

Steve,

I didn't know how to make index work.

If you are saying you don't know how to use indexes, I would suggest that you take a few moments to learn. It's hard to imagine developing a VFP application without them. Apart from being a much better way of ordering a table than the SORT command, they will make your queries run an order of magnitude faster.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk