Why is my Access Max() subquery not working?

Question

I have a Microsoft Access table in which I want to return a set of records, but only for the most recent date.

My data has 24 records with 12 of these have RECORD_DATE=#23/04/2020#, the other 12 have RECORD_DATE=#24/04/2020# (ie. 12 with 23-April, 12 with 24-April); the field is set as Date/Time.

I have written a subquery to get the Max date for each record.

The problem I have is that my query executes, but returns all the records in the table (well, it applies the "WHERE Q.SITE_ID=?" correctly), not just those with the MAX(RECORD_DATE).

Here is the SQL:

SELECT 
    Q.CONSUMABLE_RECORD_ID,
    Q.SITE_ID,
    Q.CONSUMABLE_STORAGE_ID,
    Q.CONSUMABLE_PRODUCT_ID,
    Q.CONSUMABLE_MEASUREMENT_UNIT_ID,
    Q.RECORD_DATE,
    Q.RECORD_VALUE,
    Q.LAST_DATE,
    Q.LAST_VALUE 
FROM 
    CONSUMABLE_RECORD AS Q 
WHERE 
    Q.SITE_ID =? 
AND 
    Q.RECORD_DATE=
    (
    SELECT 
        MAX(S.RECORD_DATE) 
    FROM 
        CONSUMABLE_RECORD AS S 
    WHERE 
        Q.CONSUMABLE_RECORD_ID=S.CONSUMABLE_RECORD_ID
    )

If it makes any difference, CONSUMABLE_RECORD_ID is the Primary Key, and I am executing the query using and OleDbCommand via C#, and the data provider I am using is Microsoft.ACE.OLEDB.16.0. I've also tried using MAX(CONSUMABLE_RECORD_ID) in the subquery, but that didn't work - I'd prefer to keep it as Max(RECORD_DATE) as theoretically records could be entered out-of-sequence on date.

What do I need to do to get this working? I tried 'TOP 1' in the subquery, and still get the 24 rows back.

I would prefer to keep this as a subquery rather than an Inner Join etc.

Edit:
This is different from This answer. In that one, they are looking for the top 3 results in a group - I just want the maximum record. I tried adding TOP 1 to the start of the question, and and ORDER BY (which necessitates a GROUP BY), but I'm still getting all the records back.

Edit 2: Sample Data:

The desired result is just all the records with RECORD_DATE=#24/4/20#.

Does this answer your question? [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) — June7, Apr 25 '20 at 03:48
Part of issue might be international date structure in parameter. Review http://allenbrowne.com/ser-36.html — June7, Apr 25 '20 at 04:02
How would I fix that? I'm not passing the data as a parameter (and it works fine using OleDbParameter anyway, in other code). I tried max(CONSUMABLE_RECORD_ID) which should work (and if it did, would confirm that it is a date format issue), but that doesn't work. Suggests that there is something fundamental about the subquery structure. — ainwood, Apr 25 '20 at 04:10

score 1 · Answer 1 · answered Apr 25 '20 at 05:42

1

As @June7 has suggested, you can use TOP in the sub-query:

SELECT Q.Consumable_Record_ID, 
    Q.Site_ID, 
    Q.Consumable_Storage_ID, 
    Q.Consumable_Product_ID, 
    Q.Consumable_Measurement_Unit_ID, 
    Q.Record_Date, 
    Q.Record_Value, 
    Q.Last_Date, 
    Q.Last_Value
FROM Consumable_Record Q
WHERE Q.Site_ID=?
AND Q.Record_Date=(SELECT DISTINCT TOP 1 R.Record_Date FROM Consumable_Record R ORDER BY R.Record_Date DESC);

Regards,

answered Apr 25 '20 at 05:42

Applecore

3,934
2
9
13

When I first tried this, I changed `Max()` to `Distinct Top 1` in my query, and still got all the records back. I then tried your SQL direct, and it worked. SO I looked at what else was different. `AS S` vs just declaring the alias `S` made no difference, so I removed `WHERE Q.CONSUMABLE_RECORD_ID=S.CONSUMABLE_RECORD_ID`, and then it worked. I removed that in my original Max() query, and it worked correctly there too. Thanks for the steer - any explanation of why the join in the subquery is **not** required? – ainwood Apr 25 '20 at 06:54
1

If you think about what you are asking the sub-query to return, you are asking it to return the most recent date - you don't actually care (in the sub-query part at least) about anything else. – Applecore Apr 25 '20 at 08:04

score 1 · Accepted Answer · answered Apr 25 '20 at 12:57

1

It would seem that you don't want a correlated subquery. This seems like a concise way of writing what you want:

SELECT cr.*
FROM Consumable_Record as cr
WHERE cr.Site_ID = ? AND
      cr.Record_Date = (SELECT MAX(cr2.Record_Date)
                        FROM Consumable_Record as cr2
                       );

Note the use of meaningful table aliases. Q and S don't mean anything with respect to your data. CR is an abbreviation for the table name.

The above will not return records if the site doesn't have any on that day. If you want the most recent records per site, then use that in the correlation clause:

SELECT cr.*
FROM Consumable_Record as cr
WHERE cr.Site_ID = ? AND
      cr.Record_Date = (SELECT MAX(cr2.Record_Date)
                        FROM Consumable_Record as cr2
                        WHERE cr2.Site_Id = cr.Site_Id
                       );

I suspect this is what you really want.

answered Apr 25 '20 at 12:57

Gordon Linoff

1,242,037
58
646
786

Thanks. I take the point re meaningful table aliases, but I chose Q and S for Query and Subquery. My code is actually dynamic, similar to an entity framework, so I want a generic query where I can call method where I pass the table name and the field that I want to use in the Max() subquery. – ainwood Apr 28 '20 at 19:17
So for the constraint in the subquery, I think I now understand why, in my example, joining on the primary key means that every record gets returned. Does this mean that it attempts to find the Max(record_date) for every single record in the main query, hence I just get everything back? – ainwood Apr 28 '20 at 19:23
@ainwood . . . Basically yes. Your correlation clause chooses one record. The dates match on that record, so everything ends up being selected -- one comparison at a time. – Gordon Linoff Apr 28 '20 at 22:32

Why is my Access Max() subquery not working?

2 Answers2