Oracle: getting maximum group value?

Question

For a table like this, which query will have the latest calibration information for each monitor? In other words, I want to find the maximum date value for each of the monitors. Oracle-specific functionality is appropriate for my application.

monitor_id calibration_date value ---------- ---------------- ----- 1 2011/10/22 15 1 2012/01/01 16 1 2012/01/20 17 2 2011/10/22 18 2 2012/01/02 19 

The results for this example will look like this:

 1 2012/01/20 17 2 2012/01/02 19 

Answer 1

I would 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 can also use correlated subqueries, although this 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) 

Answer 2

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_value FROM mytable; 

An option would be to use the FIRST_VALUE syntax for latest_calibration_date . In any case, it works.

Answer 3

The solution of window functions should be the most efficient and lead to only one scan of a table or index. The one I post here, I think, wins some points to be intuitive and understandable. I tested on the SQL server and performed the functions of the 2nd window, which led to two index scans.

 SELECT T1.monitor_id, T1.calibration_date, T1.value FROM someTable AS T1 WHERE 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 this, here is another one along the same lines, but less effective (63% against 37%) than the other (again in the sql server). In this case, the "Left outer join" is used in the execution plan, where the "Anti-merge" union is used as the first:

 SELECT T1.monitor_id, T1.calibration_date, T1.value FROM someTable AS T1 LEFT JOIN someTable AS T2 ON T2.monitor_id = T1.monitor_id AND T2.value > T1.value WHERE T2.monitor_id IS NULL GROUP BY T1.monitor_id, T1.calibration_date, T1.value 

Answer 4

 select monitor_id, calibration_date, value from table where calibration_date in( select max(calibration_date) as calibration_date from table group by monitor_id )