甲骨文:正在获得一个团队的最大价值? [英] Oracle: getting maximum value of a group?

问题描述

给出这样一个表，每个监视器的最新校准信息将是什么查询?换句话说，我想找到每个监视器的最大日期值. Oracle特定的功能适合我的应用程序.

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

此示例的结果如下:

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

推荐答案

我倾向于使用解析函数

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)

这篇关于甲骨文:正在获得一个团队的最大价值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！