MS Access - сгруппированный запрос на основе поля MAX

Question

Я пытаюсь построить запрос SQL, который принимает следующие данные:

+-------------+--------+---------+---------+--------+
| 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     |
+-------------+--------+---------+---------+--------+

И возвращает идентификатор последней версии и соответствующие ей данные, где проверяется критерий Fruit .

Строка SQL потребуется для возврата идентификатора с классом для версии Max, где Fruit = No

Результаты:

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

Мне нужно только вернуть идентификатор и его класс для хранения в списке MS Access.

Мне удалось построить запрос Group By / Max в редакторе, и я смог получить только идентификаторы, возвращаемые сгруппированными, но соответствующие данные не были связаны с максимальной версией.

Спасибо за вашу помощь и опыт.

Answer 1

Есть несколько способов, которыми вы можете достичь этого.

Все следующие примеры предполагают, что ваша таблица называется table1 и что ваши поля - это id , class , version и fruit (а не fruit?) - измените их в соответствии с вашими данными.

Примечание: в следующих примерах предполагается, что ваше поле fruit является текстовым полем. Если ваше поле fruit самом деле является логическим (да / нет) полем, удалите одинарные кавычки вокруг 'No' в следующих примерах.

---

Используя объединенный подзапрос:

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

Здесь подзапрос выбирает наибольшую version для каждого id для записей, где fruit = 'No' и затем он присоединяется к полному набору данных, чтобы вернуть обязательные поля для каждого id и version .

---

Используя коррелированный подзапрос:

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)

Здесь выбор выполняется полностью в WHERE , которое для каждой записи проверяет, существует ли в наборе другая запись с тем же id и большей version , и если да, то запись не возвращается.

---

Использование LEFT JOIN с неравными критериями соединения:

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

Этот пример может быть представлен только в MS Access в представлении SQL, поскольку конструктор запросов MS Access не может отображать объединения, которые имеют равные критерии соединения (т. Е. Когда одно поле равно другому).

Этот пример в действии аналогичен коррелированному подзапросу, но выбор выполняется соединением, а не в WHERE .

---

Наконец, обратите внимание, что приведенный вами пример неправильный: class для максимальной version для id = 'Onion' должен быть WEST , а не NORTH .