Access SQL查询:查找表中每个不同条目的最新日期的行 [英] Access SQL query: find the row with the most recent date for each distinct entry in a table

问题描述

全部

我确定这是一个非常简单的SQL查询问题，但是我确定这样做有一个很好的方法，也很糟糕.留给我自己的设备，我有可能以后者结束.所以...

I'm sure this is a pretty simple SQL query question, but I'm sure there's a good way, and a very BAD way, to do this. Left to my own devices, I'm liable to end up with the latter. So...

我在Access中有一个表，其中的数据如下所示:

I have a table in Access with data that looks like this:

ID      Value  As_of
1173    156    20090601
1173    173    20081201
1173    307    20080901
1173    305    20080601
127     209    20090301
127     103    20081201
127     113    20080901
127     113    20080601
1271    166    20090201
1271    172    20081201
1271    170    20080901
1271    180    20080601
...

我想要获取的是每个具有最新截止日期"(采用YYYYMM格式)的唯一ID的值".

What I'd like to get is the "Value" for each unique ID with the most recent "As Of" date (which is in YYYYMM format).

因此，我的结果集应如下所示:

So, my result set should look like this:

ID      Value    As_of
1173    156      20090601
127     209      20090301
1271    166      20090201

请注意，不同的ID将具有不同的截止日期"日期.换句话说，我不能简单地从全局上标识最近的日期，然后选择具有该日期的每一行.

Note that different IDs will have different "As Of" dates. In other words, I can't simply indentify the most recent as of globally, then select every row with that date.

该表的总价值约为200,000行，并且具有大约10,000个唯一ID.

For what it's worth, this table has about 200,000 total rows, and about 10,000 unique IDs.

非常感谢！

推荐答案

如果同时需要日期和值，则需要进行联接:

If you need both the date and the value, you need to do a join:

SELECT ID, Value,As_of 
from yourTable a inner join 
          (SELECT ID, MAX(As_of) as As_of 
          from yourTable group by ID) b 
on a.ID=b.ID and a.As_of = b.As_of

这篇关于Access SQL查询:查找表中每个不同条目的最新日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！