Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Sep 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    [Oracle] using the MAX function

    Hi...thanks in advance for any help.

    I have a program that asks a number of questions. As they answer a question, the value is placed in the database. If they are unsatisfied with an answer, they can "back up" and answer the question again, and that new answer is then stored in the database. Values are not overwritten, however, they are date/time stamped. Later in the program, I need to find the two answers that had the highest scores. However, I also only want to look at the most recent answer for any question (in case someone backed up).

    Currently, my code looks something like this:

    Select VARIABLE, VARVALUE, CALL_DATE from RESULTS
    where PATIENT_ID = 1 and VARVALUE >1
    and CALL_DATE = (select max(CALL_DATE) from RESULTS
    where patient_id = 1 and VARVALUE >1)
    order by 2 desc

    I'm sure you already know that this will only return one value, namely the last thing that was entered. How do I get it to return all of the most recent answers?

    -Micci
    Last edited by micci73; 09-29-2006 at 04:06 PM.

  • #2
    New to the CF scene
    Join Date
    Sep 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    got it!

    I'm sorry that I may have forgotten to mention that my RESULTS table has a result_id (although you may have assumed that there was a primary key). Anyway, this is what I changed to make my query work.

    Select VARIABLE, VARVALUE from RESULTS
    where PATIENT_ID = 1 and VARVALUE >1
    and RESULT_ID in (select max(RESULT_ID) from RESULTS
    where patient_id = 1 group by VARIABLE)
    order by 2 desc

    and it would work this way as well.

    Select VARIABLE, VARVALUE from RESULTS
    where PATIENT_ID = 1 and VARVALUE >1
    and CALL_DATE in (select max(CALL_DATE) from RESULTS
    where patient_id = 1 group by VARIABLE)
    order by 2 desc

    Thanks for looking.

    -Micci
    Last edited by micci73; 09-29-2006 at 04:24 PM.


  •  

    Posting Permissions

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