Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Unanswered: Order by in subquery

    I need to write a query for the following scenario
    I have the following 2 tables
    Patients - all patients in the clinic
    Adm - All visits for each patient. A patient could have more than one visit.

    Ex: Data in Patients (Fields uniq_id, patient_id, patient_name)
    1, p001, name1
    2, p002, name2
    3, p003, name3

    Data in Adm (Fields uniq_id, patient_id, start_date, end_date)
    1, p001, 01/01/2000, 01/20/2000
    2, p001, 02/11/2001, 03/11/2001
    3, p002, 12/23/2006, 12/25/2006
    4, p003, 10/15,2007, 11/15/2007
    5, p001, 07/11/2003, 08/13/2004
    6, p002, 04/04/2007, 04/17/2007
    7, p002, 05/11/2007,

    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)
    FROM patients

    Not sure how to fix this. Any help is greatly appreciated.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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).
    Code:
    select 
      p.patient_id,
      p.patient_name,
      a.start_date,
      a.end_date
    from patients p,
         adm a
    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
                       );

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •