How To Get A Field Value Based On The Max Of Another Field In VFP v8.0

Posted by DaveB on Stack Overflow See other posts from Stack Overflow or by DaveB
Published on 2010-05-06T16:28:48Z Indexed on 2010/05/06 16:48 UTC
Read the original article Hit count: 331

Filed under:
|
|

So, I have a table and I want to get the value from one field in the record with the greatest DateTime() value in another field and where still another field is equal to a certain value.

Example data:

Balance     Created                      MeterNumber
7924.252    02/02/2010 10:31:48 AM       2743800
7924.243    02/02/2010 11:01:37 AM       2743876 
7924.227    02/02/2010 03:55:50 PM       2743876 

I want to get the balance for a record with the greatest created datetime for a specific meter number. In VFP 7 I can use:

SELECT a.balance ,MAX(a.created) FROM MyTable a WHERE a.meternumber = '2743876'

But, in the VFP v8.0 OleDb driver I am using in my ASP.NET page I must conform to VFP 8 which says you must have a GROUP BY listing each non aggregate field listed in the SELECT. This would return a record for each balance if I added GROUP BY a.balance to my query.

Yes, I could issue a SET ENGINEBEHAVIOR 70 but I wanted to know if this could be done without having to revert to a previous version?

© Stack Overflow or respective owner

Related posts about foxpro

Related posts about visual-foxpro