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?
EDIT I did get Frank Perez query to work but only after converting the DateTime fields to Character for the SQL IN clause. Note the wrapping of the DateTime fields with the TTOC() function.
SELECT ;
MyTable.created, ;
MyTable.balance ;
FROM ;
MyTable ;
WHERE ;
( MyTable.meternumber = '2743876' ) ;
AND ( TTOC(MyTable.created) IN (SELECT TTOC(MAX(created)) FROM MyTable WHERE (meternumber = '2743876')) ) ;