VFP querying records with same ID

Hi all,

I have a table with the same id numbers and different dates(ex. below)

ID field1 field2...............date

1 01/03/06

1 09/04/05

1 03/13/06

3 05/25/04

5 12/12/06

5 08/05/06

I need one record for every id with the max date(date closest from today)

ID field1 field2...............date

1 03/13/06

3 05/25/04

5 12/12/06

Please help if possible, I've been trying inner join, but I can't get past a syntax error

Mike

billyzelsnack

Cetin

Yes. I realized after I did the initial reply that it could be handled with a simpler query (i.e. one that would not require a GROUP BY in the sub-query) than I first thought and forgot to edit the text.

Thanks. I will correct it.
madhu mudunuri

To do this you need to use either a correlated sub-query or you need multiple queries.

Here is the Sub-Query based solution:

SELECT id, field1, field2.....fieldn
FROM [table_name] XX ;
WHERE date = (SELECT MAX(date) FROM table_name WHERE id = XX.id )

Otherwise you are going to have to figure out a way to identify the record that contains the latest date - but I can't tell from this data what that would be.

IF you want to use the old VFP-specific behavior (VFP 7.0 or earlier) you also could try:

SET ENGINEBEHAVIOR 70

SELECT id, field1, field2, MAX( date ) ;
FROM [table_name] ;
GROUP BY id
Andrew Williams

All part of the service. (Though it is nice to guess right once in awhile)

Of course, sometimes free advice is worth everything you pay for it
Mumtaz

AndyKr,

I can't express how much i appreciate your quick response. Worked like a charm.
SgtJake
Andy,

FYI that correlated subquery is available since fox2x and not new to VFP9 (around VFP5-6 days was made available as a tip on foxyclasses site).
VFP querying records with same ID