I need to write a query to link the 2 tables by patient_id and get the latest end_date from the Adm table for each patient.
Result should be:
p001, name1, 07/11/2003, 08/13/2004
p002, name2, 05/11/2007
p003, name3, 10/15,2007, 11/15/2007
I wrote a subquery for this and got the error "missing right parenthesis" when I used the order by clause.
My SQL :
SELECT patients.patient_id, (select adm.end_date from adm
where patients.patient_id = adm.patient_id and rownum <=1 order by adm.end_date)
Not sure how to fix this. Any help is greatly appreciated.
How about this? (Untested; I didn't feel like typing sample data you posted. Next time, please, provide a proper test case which includes CREATE TABLE and INSERT INTO sample records).
from patients p,
where p.patient_id = a.patient_id
and a.end_date = (select max(a1.end_date)
from adm a1
where a1.patient_id = a.patient_id
Thanks a million for the reply. I had to do little tweaking, for records that don't have an end date.
Sorry for not posting create and insert to statements, as this is a third party application. It's done by the application and I have no access to those statements. I only have permission to query the database.
A test case is what it name says; CREATE TABLE and INSERT INTO statements aren't supposed to present real data but imaginary, sample data, but those that would make it possible for the rest of the community to work with these data. Exactly what you posted - only if you did that in a form of previously mentioned CREATE TABLE and INSERT INTO statements.