查找最大值并在MS Access中显示不同字段中的对应值 [英] Find max value and show corresponding value from different field in MS Access

问题描述

所以我在(

So I found this similar question and answer at (Find max value and show corresponding value from different field in SQL server) but I want to take it one step further. I want to get the latest date for each ID and the corresponding TYPE instead of just the absolute max for all entries. Any suggestions?

ID      Type        Date
1       Initial      1/5/15 
1       Periodic     3/5/15
2       Initial      2/5/15  
3       Initial      1/10/15
3       Periodic     3/6/15  
4       Initial      3/8/15 

下面的代码显示了如何仅获取所有条目的最大日期，但是我想要每个ID以及相应类型的最大日期.

The code below shows how to get just the max date of ALL entries, but I want the max date for each ID and then the corresponding Type.

select id, type, date
from yourtable
where date in (select max(date)
                     from yourtable)

OR

select id, type, date
from yourtable t1
inner join
(
  select max(date) maxdate
  from yourtable
) t2
  on t1.date = t2.maxdate;

推荐答案

您可以将第一种方法与相关子查询一起使用:

You can use the first method with a correlated subquery:

select id, type, date
from yourtable as t
where date in (select max(date)
               from yourtable as t2
               where t2.id = t.id);

或者，在第二个中按id分组:

Or, group by id in the second:

select t1.id, t1.type, t1.date
from yourtable as t1 inner join
     (select id, max(date) maxdate
      from yourtable
      group by id
     ) t2
     on t1.date = t2.maxdate and t1.id = t2.id;

这篇关于查找最大值并在MS Access中显示不同字段中的对应值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！