Search code examples
sqlms-access

Grouped multi output access query problem

A have a table that stores incoming case files and data for those case files, like case id, file id, date, file path and others.

I want to write a query that shows the case id and its file id for the latest entry.

I can create a query that show the grouped case id and max date, but I can't figure out how to show the file id for the last filed...file.

Here is the SQL for what I could do:

SELECT 
    iktat.caseid, 
    MAX(iktat.date) AS MaxOfdate 
FROM 
    iktat 
GROUP BY 
    iktat.caseid;

The data that I have

Case ID File ID Date
ID0001 1254 2022/01/02
ID0001 0657 2024/02/02

What I want to achieve:

Case ID File ID Date
ID0001 0657 2024/02/02
Solution 
  • SELECT iktat.caseid, iktat.fileid, iktat.date
FROM iktat
WHERE iktat.date = (
    SELECT Max(date)
    FROM iktat AS i
    WHERE i.caseid = iktat.caseid
);

    Demo: https://dbfiddle.uk/hSjB9nKt