[Oracle] using the MAX function

[micci73]

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

[micci73]

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