sql - 查询中的多个最大值

Question

我知道标题听起来不太具有描述性，但这是我能想到的最好的标题:

我有这张 table

ID     BDATE      VALUE28911  14/4/2009  4482028911  17/4/2009  3224028911  20/4/2009  3055028911  22/4/2009  4422587,528911  23/4/2009  444165928911  24/4/2009  7749594,6738537  17/4/2009  5828038537  20/4/2009  13724038537  22/4/2009  8109869238605  14/4/2009  272236838605  20/4/2009  560038605  22/4/2009  162540038605  23/4/2009  6936575

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

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

ID     BDATE      VALUE28911  24/4/2009  7749594,6738537  22/4/2009  8109869238605  23/4/2009  6936575

I have already tried

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

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

Answer 1

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

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