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

FunCoder

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.

Gordon Linoff

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

Named Query to SELECT rows with MAX(column name), DISTINCT by another column

How can I select rows corresponding to the unique pair of column values with the highest value of another column in PostgreSQL?

How can I SELECT rows with MAX(Column value) in DQL?

How can I replace a column of values by another with different number of rows?

Select distinct rows with max count another row

How can I select distinct by one column?

How can I select distinct column combinations from a DataTable object with another column as a condition?

How to select n rows such as the values of one given column are all distinct?

How do I select distinct rows and cross check in another table?

How can I count occurences of different integer values (in a particular column) for rows with different factor values(another column)?

Select distinct rows with same value in another column

How can I get the max value from one column where values match another column?

How can I aggregate a column's value based on the min() and max() values of another column?

how to access rows of df depending on values of another column in another df

How can I average every 5 rows specific column and select last data from another column in Pandas

How can I subset a dataframe to rows relative to column values from another inside a function where that column is a parameter?

In Pandas how do I select rows that have a duplicate in one column but different values in another?

In Oracle SQL how can i find all values in one column for which in another column exist more than one distinct value

How to select rows that have certain values present in another column

How can I get distinct values of a specific column in arrayList

How can I distinct count values in a column in R

How can I access particular column values?

How can I select a data from another column from rows that have been selected?

How can I select all rows based on the AVG calculated in another column?

How can I select all the rows which do not share a column value with another row which is null?

SELECT rows with MAX(column value) with corresponding time, DISTINCT by name

Select distinct rows with max date with repeated and null values (Oracle)

TOP Ranking

HotTag

Archive