查询中的多个最大值 [英] Multiple max values in a query

问题描述

我知道标题听起来描述性不强，但这是我能想到的最好的东西

I know the title does not sound very descriptive, but it is the best I could think of:

我有这张桌子

ID     BDATE      VALUE
28911  14/4/2009  44820
28911  17/4/2009  32240
28911  20/4/2009  30550
28911  22/4/2009  4422587,5
28911  23/4/2009  4441659
28911  24/4/2009  7749594,67
38537  17/4/2009  58280
38537  20/4/2009  137240
38537  22/4/2009  81098692
38605  14/4/2009  2722368
38605  20/4/2009  5600
38605  22/4/2009  1625400
38605  23/4/2009  6936575

这实际上是一个封装在视图中的非常复杂的查询，但是现在不重要了.

which is in fact a very complicated query encapsulated in a view, but it is not of the matter now.

我想为每个ID包含最高BDate的行.在此示例中，这将是结果.

I would like to have for each ID, the row containing the highest BDate. In this example, this would be the result.

ID     BDATE      VALUE
28911  24/4/2009  7749594,67
38537  22/4/2009  81098692
38605  23/4/2009  6936575

我已经尝试过

select id, max(bdate), value from myview group by id, value

但它返回所有行，因为对于每个值，列的值是不同的.该查询是在Oracle v10中设计的，我有资格仅使用选择查询而不创建过程.

but then it returns all the rows, because for each the value collumn is different. This query is designed in Oracle v10, and I am eligible to use only select queries and not to create procedures.

推荐答案

我们可以在IN子句中使用乘法列:

We can use multiply columns in an IN clause:

select id, bdate, value 
from myview 
where (id, bdate) in
    (select id, max(bdate)
     from myview group by id)
/

这篇关于查询中的多个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！