访问SQL查询：查找表中每个不同条目的最近日期的行

All,

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...

我确信这是一个非常简单的SQL查询问题,但我确信这是一种很好的方法,也是一种非常糟糕的方法。留给我自己的设备,我很可能最终得到后者。所以...

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

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

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
...

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).

我想得到的是每个唯一ID的“Value”,其中包含最新的“As Of”日期(采用YYYYMM格式)。

So, my result set should look like this:

所以,我的结果集应如下所示:

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

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.

请注意,不同的ID将具有不同的“截止日期”日期。换句话说,我不能简单地识别全球最新的,然后选择该日期的每一行。

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

对于它的价值,该表总共有大约200,000行,以及大约10,000个唯一ID。

Many thanks in advance!

提前谢谢了!

5 个解决方案

#1

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

#2

@Funka, that will not work if you have duplicate "value" values for different ID's - that will basically give you a grouped list by "value", not by id...

@Funka,如果你有不同ID的重复“值”值,这将无效 - 这基本上会给你一个按“值”分组的列表,而不是id ...

@Joe Fair, aggregates aren't allowed in where clauses without a subquery/having combo as well, at least not in ANSI...

@Joe Fair,聚合不允许在没有子查询/有组合的where子句中,至少不在ANSI中...

This will give you the list, but will give duplicates as well if you have multiple rows with the same id/As_of values:

这将为您提供列表,但如果您有多个具有相同id / As_of值的行,也会给出重复项:

select  t1.id, t1.value, t1.As_of
from    tableName t1
join    (
        select  id as id, max(As_of) as max_as_of
        from    tableName
        group by id
    ) t2
on      t1.id = t2.id
and     t1.As_of = t2.max_as_of

If you want to remove duplicates from that, you'd just want to add a distinct to the top select, like this:

如果你想从中删除重复项,你只想在顶部选择中添加一个不同的选项,如下所示:

select  distinct t1.id, t1.value, t1.As_of
from    tableName t1
join    (
        select  id as id, max(As_of) as max_as_of
        from    tableName
        group by id
    ) t2
on      t1.id = t2.id
and     t1.As_of = t2.max_as_of

#3

Try something like this

尝试这样的事情

SELECT t1.*
FROM (SELECT Table1.ID, Max(Table1.As_Of) AS MaxOfAs_Of
FROM Table1
GROUP BY Table1.ID
)  AS MaxIDS INNER JOIN Table1 t1 ON MaxIDS.ID = t1.ID
and MaxIDS.MaxOfAs_Of = t1.As_Of

#4

I think what you're looking for is this:

我想你要找的是:

select id, value, as_of from table_name where as_of = max(as_of) group by id

从table_name中选择id,value,as_of,其中as_of = max(as_of)group by id

This says for each id, find the max as_of, and get that value.

这表示对于每个id,找到max as_of,并获取该值。

This is generic sql. I'm not sure about access. I'm sure if this doesn't work there is something similar.

这是通用的sql。我不确定访问权限。我敢肯定,如果这不起作用,有类似的东西。

Good luck! Joe

祝好运!乔

#5

Not sure what platform you're looking to do this on, but in T-SQL you can do the following:

不确定您要在哪个平台上执行此操作,但在T-SQL中,您可以执行以下操作:

SELECT t.*
FROM (
    SELECT ID, MAX(As_Of) as r1
    FROM myTable
    GROUP BY ID
    ) as dt
INNER JOIN myTable t ON dt.ID = t.ID and dt.r1 = t.As_Of

Good luck!

EDIT: Well my poor answer was bothering me so i've fixed it, even though this answer already exists elsewhere on the page now.

编辑:好吧,我糟糕的回答困扰着我,所以我已经修好了,尽管这个答案已经存在于页面的其他地方了。

#1