Oracle: getting maximum value of a group?

I'd tend to use analytic functions

SELECT monitor_id,       host_name,       calibration_date,       value  FROM (SELECT b.monitor_id,               b.host_name,               a.calibration_date,               a.value,               rank() over (partition by b.monitor_id order by a.calibration_date desc) rnk          FROM table_name a,               table_name2 b         WHERE a.some_key = b.some_key) WHERE rnk = 1

You could also use correlated subqueries though that will be less efficient

SELECT monitor_id,       calibration_date,       value  FROM table_name a WHERE a.calibration_date = (SELECT MAX(b.calibration_date)                               FROM table_name b                              WHERE a.monitor_id = b.monitor_id)

My personal preference is this:

SELECT DISTINCT       monitor_id      ,MAX(calibration_date)       OVER (PARTITION BY monitor_id)       AS latest_calibration_date      ,FIRST_VALUE(value)       OVER (PARTITION BY monitor_id             ORDER BY calibration_date DESC)       AS latest_valueFROM mytable;

A variation would be to use the FIRST_VALUE syntax for latest_calibration_date as well. Either way works.

The window functions solution should be the most efficient and result in only one table or index scan. The one I am posting here i think wins some points for being intuitive and easy to understand. I tested on SQL server and it performed 2nd to window functions, resulting in two index scans.

SELECT T1.monitor_id, T1.calibration_date, T1.valueFROM someTable AS T1WHERE NOT EXISTS (    SELECT *     FROM someTable AS T2    WHERE T2.monitor_id = T1.monitor_id AND T2.value > T1.value) GROUP BY T1.monitor_id, T1.calibration_date, T1.value

And just for the heck of it, here's another one along the same lines, but less performing (63% cost vs 37%) than the other (again in sql server). This one uses a Left Outer Join in the execution plan where as the first one uses an Anti-Semi Merge Join:

SELECT T1.monitor_id, T1.calibration_date, T1.valueFROM someTable AS T1LEFT JOIN someTable AS T2 ON T2.monitor_id = T1.monitor_id AND T2.value > T1.valueWHERE T2.monitor_id IS NULLGROUP BY T1.monitor_id, T1.calibration_date, T1.value