How To Get A Field Value Based On The Max Of Another Field In VFP v8.0
- by DaveB
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?
