Q
Problem solve Get help with specific problems with your technologies, process and projects.

Last value in group

I would like to get the last value within a group in the Oracle GROUP BY query. I can't use MAX(VALUE) as I need the most recent value in the group.

I would like to get the last value within a group in the Oracle GROUP BY query. For example:
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 DESC
I can't use MAX(VALUE) as I need the most recent value in the group.
The "most recent" value will be the one with the highest result date, so you can use the MAX function here. Try this query:
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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close