views:

134

answers:

2

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')) ) ;
A: 
select 
      YT.Balance,
      YT.Created
   from 
      YourTable YT
   where 
          YT.MeterNumber = '2743876'
      and YT.Created in
              ( select 
                      max( YT2.Created ) Created
                   from 
                      YourTable YT2
                   where 
                      YT.MeterNumber = YT2.MeterNumber 
              )

Remove the first "YT.MeterNumber = '2743876' and" from the where clause and you'll get all balances for all meters...

Additionally, if run from VFP directly to test, just add ";" line continuation at the end of each line. It should be complient no problem with VFP6, 7, 8 or 9.

Per your other comments, if this query is always going to be called based on a SINGLE MeterNumber, you could adjust as

select 
      YT.Balance,
      YT.Created
   from 
      YourTable YT,
      ( select YT2.MeterNumber,
               max( YT2.Created ) Created
           from 
               YourTable YT2
           where 
               YT2.MeterNumber = '2743876' 
           group by 
               1 ) YT3
   where 
          YT.MeterNumber = '2743876'
      and YT.MeterNumber = YT3.MeterNumber
      and YT.Created = YT3.Created
DRapp
I tried your query in VFP 7 on my table of 264,966 records. After 5 minutes of execution with audible disk drive activity, a "Not enough memory for file map" error was raised.
DaveB
how many records are in your table for the single ID key of 2743876.. if in VFP, is there an INDEX TAG on the MeterNumber to take advantage of it?
DRapp
There are 5499 records with MeterNumber = '2743876' and there is a index of ASC with collate set to machine on the MeterNumber field which does not allow nulls.
DaveB
It SHOULD be almost instantaneous on the query because the INNER query relies on a value from the OUTER main query... and the outer main query is for the specific key in question.
DRapp
A: 

DRapp is correct in that if there is not an index on MeterNumber, this query will be slow. However, if you are only trying to get the balance for one particular meter, the following query will return the same results and should be quicker.

SELECT ;
    MyTable.created, ;
    MyTable.balance ;
FROM ;
    MyTable ;
WHERE ;
    ( MyTable.meternumber = '2743876' ) ;
    AND ( MyTable.created IN (SELECT MAX(created) FROM MyTable WHERE (meternumber = '2743876')) ) ;
Frank Perez
Correct on applying the MeterNumber to BOTH parts of the query. However, it should still be flexible in case Dave is looking for an everybody's balance
DRapp
I tested this query from VFP 7. Runs fast but returns no records. It seems that the created DateTime field is not being found in the subquery even though a record exists with the matching DateTime value. Before posting this question, I had the same problem with my queries and thought I must be missing something.
DaveB
@Frank Perez - I did get your query to work with a slight modification. See the edit to my question. Any thoughts or comments anyone?
DaveB