>>I have a situation that occurs frequently in an application that a group of vfp programmers are working on. Lets say you have two tables (patients and admissions) that belong to a database. For the sake of simplicity, lets say the patients table contains the patient number (C(7)) and the admission table contains the patient number and an admission date. I want to join the two tables and only retrieve the latest admission record.
>>
>>SELECT ;
>> pat.cPatientNum, ;
>> adm.dAdmissionDate ;
>> FROM ;
>> Patients!Patients pat ;
>> INNER JOIN Patients!Admissions adm ;
>> ON pat.cPatientNum = adm.cPatientNum ;
>> ORDER BY ;
>> pat.cPatientNum, ;
>> adm.dAdmissionDate DESCENDING ;
>> INTO ;
>> CURSOR temp
>>
>>This resultant query is almost what I want. I get a list of all admissions for each patient sorted in descending order of admission date. So the first record for each patient contains the latest admission date. How can I eliminate the other admission records for the patient? I can run another query on the above query to group by the patient number and the admission date. This creates the result that I want. However, I want to do it in one query not two.
>>
>>If I use a group by in the original query, I don't get the latest admission record since the order by clause is a post query operation. It orders the query after the grouping, which is not what I want.
>>
>>Any Ideas?
>>
>
>

SELECT cPatientNum, MAX(dAdmissionDate) AS dLatestAdm ;
>  FROM Patients!Admissions ;
> GROUP BY cPatientNum ;
>  INTO CURSOR TEMP NOFILTER

thanks for the quick reply. I'll try it.
thanks again
pat moran