Access SQL query: find the row with the most recent date for each distinct entry in a table

Question

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

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

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

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.

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

Many thanks in advance!

Answer 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

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

@Joe Fair, aggregates aren't allowed in where clauses without a subquery/having combo as well, at least not in 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:

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