CODEWITHSUNDEEP
sqloracle-database
Mark Harrison
Mark Harrison

Reputation: 304434

Oracle: getting maximum value of a group?

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

Upvotes: 10

Views: 40076

Answers (4)

Sergey Benner
Sergey Benner

Reputation: 4431

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
  )

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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;

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

Upvotes: 11

J Cooper
J Cooper

Reputation: 4988

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.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 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.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

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231651

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)

Upvotes: 19

Related Questions