CODEWITHSUNDEEP
sqlms-access
ainwood
ainwood

Reputation: 1048

Why is my Access Max() subquery not working?

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: Sample data and structure

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

Upvotes: 0

Views: 566

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

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.

Upvotes: 1

Applecore
Applecore

Reputation: 4099

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,

Upvotes: 1

Related Questions