>Hello,
>
>I have tried your SQL and noticed that it has dropped some of the records that should appear in the results. While the logic makes plenty of sense, the results do not.
>
>select * from pataddress t1 ;
> where t1.effective_date = (select max(t2.effective_date) from pataddress t2 ;
> where t1.patient_id = t2.patient_id) ;
> ORDER BY t1.patient_id
>
>This is my SQL statement. I know I have Patient_ID = 1 in the table twice; but Patient_ID = 1 does not appear in the results.
>
>My PatAddress table has Patient_id's from 1 to 10; but I only have in my results 2, 4, 5, 7, 9, 10.

I'm sorry w/o seeing your actual data I have no idea what you're missing. A sample I could give :

Create Cursor patients (patientID i, PK i, ef_date d)
For ix=1 To 10
  lnVisits = Max(2,Ceiling(Rand()*10))
  For jx = 1 To lnVisits
    Insert Into patients ;
      (patientID, PK, ef_date) ;
      VALUES ;
      (ix, Reccount('patients')+1,Date()-Ceiling(Rand()*100))
  Endfor
Endfor
Browse

Select * From patients p1 ;
  WHERE p1.ef_date = ;
  (Select Max(ef_date) From patients p2 ;
  WHERE p1.patientID = p2.patientID) ;
  INTO Cursor CrsMax
Browse

Cetin