>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