Search code examples
sqlms-accessnesteddistinctms-access-2016

How can I select distinct rows with max values on another column in Access 2016

I am using Access 2016 as database tool and want to be able to retrieve the latest "Version" of an "Entry" from a "DataTable". Each of these Parameters (amongst others) are separate columns in the DataTable. When I split the nested query into to different queries it works fine but as soon as I build a single query out of them it doesn't. I found similar questions asked here adressing the same problem but I guess those were not using Access 2016. Implementing the proposed Answers did not work in my case. Or I make mistake and don't realize it. So here the facts of the problem.

DataTable:

enter image description here

Desired End Result:

enter image description here

I use the following SQL expression (qry1) to filter for the entries with the highest version

SELECT DataTable.[Entry] AS EntrySorted, max(DataTable.[Version]) AS MaxVersion
FROM DataTable
GROUP BY DataTable.[Entry];

When I build a separate query (qry2) which uses the first query it works as expected

SELECT DataTable.[Entry], DataTable.[Summary]
FROM DataTable
INNER JOIN qry1
ON (DataTable.[Entry] = qry1.EntrySorted) AND (DataTable.Version = qry1.MaxVersion);

Now to the problem: As soon as I combine those two query it does not work.

SELECT DataTable.[Entry], DataTable.[Summary]
FROM DataTable
INNER JOIN
(
SELECT DataTable.[Entry] AS EntrySorted, max(DataTable.[Version]) AS MaxVersion
FROM DataTable
GROUP BY DataTable.[Entry]
)
ON (DataTable.[Entry] = EntrySorted) AND (DataTable.Version = MaxVersion);

I would highly appreciate if somebody could also explain the mistake in my approach.

Thanks a lot in advance.

Solution

  • You can use a correlated subquery:

    select dt.*
from datatable as dt
where dt.version = (select max(dt.version)
                    from datatable as dt2
                    where dt2.entry = dt.entry
                   );

    No additional table or view is needed.