Given a table like this, what query will the most recent 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 fine 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 would look like this:
1 2012/01/20 17
2 2012/01/02 19
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With