MS Access - Grouped query based on MAX field

Question

I am trying to build a SQL Query that takes the following data:

+-------------+--------+---------+---------+--------+
| Primary Key |   ID   | Version |  Class  | Fruit? |
+-------------+--------+---------+---------+--------+
|           1 | Banana |       1 | NORTH   | Yes    |
|           2 | Onion  |       1 | WEST    | No     |
|           3 | Orange |       1 | NA      | Yes    |
|           4 | Orange |       2 | PACIFIC | Yes    |
|           5 | Banana |       2 | EUR     | Yes    |
|           6 | Celery |       1 | EUR     | No     |
|           7 | Celery |       3 | SOUTH   | No     |
|           8 | Celery |       4 | SOUTH   | No     |
|           9 | Pepper |       1 | N-PAC   | No     |
|          10 | Pepper |       2 | N-PAX   | No     |
+-------------+--------+---------+---------+--------+

And returns ID of latest version and its corresponding data, where the criteria of Fruit is examined.

An SQL string would be needed to return ID with class for Max version where Fruit = No

Results:

+--------+-------+
|   ID   | Class |
+--------+-------+
| Onion  | NORTH |
| Celery | SOUTH |
| Pepper | N-PAX |
+--------+-------+

I only need to return the ID and it's class to store in an MS Access listbox.

I managed to build a Group By / Max query in the editor and was only able to get IDs to return grouped, but the corresponding data was not associated with the maximum version.

Thanks for your help and expertise.

Answer 1

There are several ways that you can achieve this.

The following examples all assume that your table is named table1 and that your fields are id, class, version and fruit (not fruit?) - change these as appropriate to suit your data.

Note: the following examples assume that your fruit field is a text field. If your fruit field is actually a boolean (yes/no) field, then remove the single quotes around 'No' in the following examples.

---

Using a joined subquery:

select u.id, u.class
from table1 u inner join
(
    select t.id, max(t.version) as mv
    from table1 t
    where t.fruit = 'No'
    group by t.id
) v on u.id = v.id and u.version = v.mv

Here, the subquery selects the greatest version for each id for records where fruit = 'No' and this is then joined to the complete dataset to return the required fields for each id and version.

---

Using a correlated subquery:

select t.id, t.class
from table1 t
where t.fruit = 'No' and not exists
(select 1 from table1 u where u.id = t.id and u.fruit = 'No' and u.version > t.version)

Here, the selection is performed entirely within the WHERE clause, which, for every record, tests whether there exists another record in the set with the same id and a greater version, and if so, the record is not returned.

---

Using a LEFT JOIN with unequal join criteria:

select t.id, t.class
from table1 t left join table1 u on t.id = u.id and t.version < u.version
where t.fruit = 'No' and u.id is null

This example can only be represented in MS Access in the SQL view, as the MS Access Query Designer cannot display joins which have equal join criteria (i.e. where one field equals another).

This example is similar in operation to the correlated subquery, but the selection is performed by the join, rather than within the WHERE clause.

---

Finally, note that your given example result is incorrect: the class for the maximum version for id = 'Onion' should be WEST, not NORTH.