MSAccess - Select max value of each group

Question

Dear All,

by the below query I get the following MSAccess data:

SELECT
    [TEMP_TABLE].Serialnumber_Or_SNID,
    [TEMP_TABLE].Warranty_Code,
    Max([TEMP_TABLE].EXTENSION_SORT_ORDER) AS MaxOfEXTENSION_SORT_ORDER
FROM
    TEMP_TABLE
GROUP BY
    [TEMP_TABLE].Serialnumber_Or_SNID,
    [TEMP_TABLE].Warranty_Code;

but I would like to see only the 'Serialnumber_Or_SNID' field value (of each group) having the max value of 'MaxOfEXTENSION_SORT_ORDER' like this:

Any suggestion?

Thanks

Answer 1

Please understand that our forum is a public platform, and we will modify your questions to cover your personal information in the description. Kindly ensure that you hide these personal or organization information next time you post error or some information to protect personal data. 

Dear @Luca Tramontana,

Welcome to Microsoft Q&A Forum!

Based on your description, please kindly try this query:

SELECT T.Serialnumber_Or_SNID,
       T.Warranty_Code,
       T.EXTENSION_SORT_ORDER
FROM TEMP_TABLE AS T
INNER JOIN (
    SELECT Serialnumber_Or_SNID,
           Max(EXTENSION_SORT_ORDER) AS MaxSortOrder
    FROM TEMP_TABLE
    GROUP BY Serialnumber_Or_SNID
) AS M
ON T.Serialnumber_Or_SNID = M.Serialnumber_Or_SNID
AND T.EXTENSION_SORT_ORDER = M.MaxSortOrder;

Here is the result in my test with this query:

Your provided query returns all combinations of Serialnumber_Or_SNID and Warranty_Code, along with the max value of EXTENSION_SORT_ORDER for that combination. But, with my query, it uses a subquery to get the maximum EXTENSION_SORT_ORDER per Serialnumber_Or_SNID and then joins back to retrieve the corresponding Warranty_Code. This is the correct approach because:

• MS Access does not support TOP 1 WITH TIES or window functions like ROW_NUMBER().
• Using GROUP BY alone cannot return the associated Warranty_Code for the max value.
• The join ensures you only get rows where EXTENSION_SORT_ORDER equals the maximum for that serial number.

This will return exactly one row per Serialnumber_Or_SNID with the highest EXTENSION_SORT_ORDER and its corresponding Warranty_Code.

Hope this helps and I wish you a pleasant day!

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.