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

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

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.

Answer 2

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

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

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

Good luck! Joe

Answer 3

@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

Answer 4

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 5

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