SELECT LAST(VALUE) AS RESULT FROM RESULTS WHERE MRC_ID = 5 AND RESULT_DATE < TO_DATE('01/01/2006', 'DD/MM/YYYY') GROUP BY RESULT_DATE ORDER BY RESULT_DATE DESCI can't use MAX(VALUE) as I need the most recent value in the group.
SELECT VALUE FROM RESULTS WHERE result_date in (SELECT MAX(RESULT_DATE) FROM RESULTS WHERE MRC_ID = 5 AND RESULT_DATE < TO_DATE('01/01/2006', 'DD/MM/YYYY'))The trick here is to get the max result date matching your criteria and then use that date to get the value from the results on that date.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.