Web Analytics Made Easy -
StatCounter [Oracle] using the MAX function - CodingForum

Announcement

Collapse
No announcement yet.

[Oracle] using the MAX function

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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; Sep 29, 2006, 12:06 PM.

  • #2
    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; Sep 29, 2006, 12:24 PM.

    Comment

    Working...
    X