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.