Use SELECT (SQL) to find the largest value of a field. 2

[AndrewMozley]

I need to find the largest value of the date in a certain set of records in a VFP table. At present I do something like this (it works).

[Blue]LOCAL lProd, lDate
USE mytable
SELECT mytable
SET ORDER TO TAG Prodcode
lDate = CTOD("01/01/1900")
lProd = PADR("PROD1",20)
SEEK lProd
SCAN WHILE mytable.prodcode = lProd
IF mytable.date > lDate
lDate = mytable.date
ENDIF
ENDSCAN[/Blue]

I feel that I ought to be using an SQL statement to do this, but up to now I have only ever used SELECT (SQL) to create a cursor, something like :

[Blue]SELECT this, that, the_other FROM somefile INTO CURSOR mycursor[/Blue]

Is there a more efficient way of finding out my latest date (using SQL)?

 

[Mike Lewis]

How about this:

SELECT MAX(DateField) FROM MyTable WHERE ProdCode = iProd

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

If you have an index on both prodcode and date field, then rushmore can make use of both and a query would be better, yes.

In this case, you just need to add WHERE clause filtering Prodcode and select MAX(date):

lProd = PADR("PROD1",20)
Select Max(date) as MaxDate FROM mytable WHERE Prodcode = m.lProd Into Cursor curMaxDate

If _tally>0 && _tally is the number of records selected
   lDate = curMaxDate.MaxDate
Else
   * no records found for that prodcode, so use default date:
   lDate = Date(1900,1,1))
Endif

The advantage over seeking and scanning is rushmore can make use of both the prodcode index to filter records with that code AND additionally can make use of an index on the date field to find the max in that range of records faster.

Bye, Olaf.

 

[Olaf Doschke]

If you want to know this max value for all prodcodes, add grouping (GROUP BY):

Select Prodcode, Max(date) As MaxDate From mytable Group By Prodcode Into Cursor curMaxDates

Browse

Bye, Olaf.

 

[AndrewMozley]

Thanks Mike and Olaf. You may have partly answered my next question, Olaf. I see that the SELECT command creates a cursor. Do I always have to use a cursor and then read it? Or can I put the result of my SELECT SQL statement into a memory variable?

That is, can I say :

[Blue]lDate = SELECT MAX(DateField) FROM MyTable WHERE ProdCode = iProd[/Blue]

. . . or am I being a bit optimistic with the syntax!

 

[Mike Lewis]

Andrew,

No, you can't SELECT straight to a memory variable (like you can in SQL Server and others).

But you can do it to an array. And, in this case, since the array will have at most one element, you can easily transfer it to a memory variable:

SELECT .... INTO ARRAY laMyArray
IF _TALLY > 0
  lDate = laMyArray(1)
ENDIF

In this particular case, you don't need the test for _TALLY, because if the result set is empty, you will get still get an array, which contains a single item that contains NULL. But in other cases, you might not get an array, hence the test.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

Either as Mike has indicated above using an Array (which are memory variables of a certain type) to contain the SQL Query value(s)...

OR

SELECT MAX(DateField) AS Max;
   FROM MyTable ;
   WHERE ProdCode = iProd ;
   INTO CURSOR Results
SELECT Results
GO TOP
lDate = Results.Max

The SQL Query value(s) cannot go directly into a single memory variable other than an array element.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Notice:

you can acces the first array element with the array name without specifying (1) or [2], so you can write code that LOOKs like it generates a single variable.

Mike also already said if a query has no result it will still create an array with a value of NULL in it. That's only true, if you select MAX(), COUNT() or some other aggregation of data, SELECT * FROM ALIAS() WHERE .F. INTO Array fdsjkfhkj does not create the array at all.

So it's good to check for _Tally before adressing the array. This is something specific to arrays, if a result is empty, but you select into cursor, you still get a cursor with no record.

Bye, Olaf.