Access query to select rows that are the MAX of one column but unique on another

Question

Lets say I want a table that gives me the 4 columns of data below, but I only want a single row for each unique "ID" using the "Date" column to select only the most recent date for each unique "ID":

|--ID--|-Type-|-Code-|--Date--|
|   1  |   A  |  11  |11/07/13|
|   2  |   A  |  11  |11/07/13|
|   2  |   B  |  12  |10/07/13|   <-- don't want this record from ID=2 as
|   3  |   A  |  11  |11/07/13|       there is a more recent date for it
|   4  |   A  |  10  |11/07/13|       than 10/07/2013
|   5  |   A  |  11  |11/07/13|

I've tried adapting this answer that seemed to be for a similar question, but I'm getting the following error:

Your query does not include the specified expression 'ID' as part of an part of an aggregate function

Here's my adapted version of that SQL I was trying to run in Access:

SELECT ESM.ID, ESM.Type, ESM.Code, ESM.Date
FROM Emp_Stat_Mon As ESM
INNER JOIN
    (
    SELECT ID, MAX(Date) AS MaxDate
    FROM Emp_Stat_Mon
    GROUP BY ID
    ) groupedESM ON ESM.ID = groupedESM.ID AND ESM.Date = groupedESM.MaxDate;

Ron.B.I · Accepted Answer · 2013-07-11T12:10:58.530

9

This does it:

SELECT  ID, Type, Code, Date
FROM Emp_Stat_Mon t1
WHERE t1.Date = 
    (SELECT Max(t2.Date) 
     FROM Emp_Stat_Mon t2 
     WHERE t2.ID=t1.ID 
     GROUP BY t2.ID)

An SQLFiddle with your sample data : SQLFiddle

edited Jul 11 '13 at 12:10

answered Jul 11 '13 at 12:03

Ron.B.I

2,726
1
20
27

thanks! works great. I forgot to put this in my question though: how do I filter on "Type" field by say "A" as well? adding `HAVING (((Type)="A"))` brings up an error "HAVING clause without grouping or aggregation". – Matt Hall Jul 11 '13 at 12:11
I'm not sure I understand what you want ? filtering by Type can simply be done by : [This Sql Example Fiddle](http://sqlfiddle.com/#!2/a0f5f/2) . is this what you wanted? – Ron.B.I Jul 11 '13 at 12:14
Sorry - I was adding the filter in design view so Access was creating its own code that didn't work. thanks for your help! – Matt Hall Jul 11 '13 at 12:17

score 0 · Answer 2 · edited May 23 '17 at 12:02

I just had to get a similar thing working. I used DMax() to get me there, but it essentially does the same thing as Ron's code.

SELECT ID, Type, Code, Date
FROM Emp_Stat_Mon As ESM
WHERE ESM.Date = DMax("Date", "Emp_Stat_Mon", "ID=" & ESM.ID);

EDIT:

As I was putting this together, I was concerned about performance. I am expecting my own table to get large, so this method should probably be avoided. I thought there was good discussion, here. I had trouble getting the second SELECT to work for me, but I think it deserves a second chance!

Access query to select rows that are the MAX of one column but unique on another

2 Answers2

Linked