Группировка по максимальной дате для записи клиента (несколько таблиц)

Question

Доброе утро всем!

У нас есть таблица, показывающая клиентов, их склады и дату последнего изменения их записи. Моя задача - найти самый последний номер клиента для конкретного склада. Большинство из них не изменились, но есть немало. Я создал приведенный ниже запрос, который отлично работает, чтобы найти дату последней поправки для всех складов в моей таблице.

Select
  WHS,
  Max(AMENDT) As MAX_AMENDT
From
  MBC6REVQ
Group By
  WHS
Order By
  WHS,
  Max(Distinct (Select
    Max(MAX_AMENDT)
  From
    MBC6REVQ
  Group By
    MBC6REVQ.WHS
  Fetch First
    1 Rows Only)) Desc

У меня проблема в том, что теперь мне необходимо добавить номер клиента и присоединиться к моей таблице клиентов, чтобы получить имя клиента. Просто добавление поля CUSNO приводит к ошибке, поэтому я должен добавить CUSNO в 'group by'. Это дает мне несколько записей для каждого склада. Мои данные выглядят так:

    WHS   AMENDT
    A01   1150101
    A01   1130704
    A02   1141030
    A07   1071101
    A10   1020905

..так что запрос правильно возвращает:

    WHS   AMENDT
    A01   1150101
    A02   1141030
    A07   1071101
    A10   1020905

Мне нужно добавить столбец CUSNO, потому что номер клиента изменился. Мой запрос выглядит так:

Select
  WHS,
  CUSNO,
  Max(AMENDT) As MAX_AMENDT
From
  MBC6REVQ
Group By
  WHS, CUSNO
Order By
  WHS,
  Max(Distinct (Select
    Max(AMENDT)
  From
    MBC6REVQ
  Group By
    MBC6REVQ.WHS
  Fetch First
    1 Rows Only)) Desc

... и возвращает:

    WHS   CUSNO   AMENDT
    A01   1003    1150101
    A01   1056    1130704
    A02   1011    1141030
    A07   1169    1071101
    A10   1012    1020905

... когда мне нужно иметь:

    WHS   CUSNO   AMENDT
    A01   1003    1150101
    A02   1011    1141030
    A07   1169    1071101
    A10   1012    1020905

Кто-нибудь может помочь? Это сводит меня с ума!!! Любая помощь очень ценится!

заранее спасибо

Matt

Answer 1

Я не знаю всех возможностей db2400, поэтому могут быть более простые / лучшие способы справиться с этим, если он поддерживает, например, оконные функции. Тем не менее, это самый "универсальный" SQL, который я могу придумать, так что он, скорее всего, сработает.

SELECT
    T1.WHS,
    T1.CUSNO,
    T1.AMENDT
FROM
    MBC6REVQ T1
LEFT OUTER JOIN MBC6REVQ T2 ON
    T2.WHS = T1.WHS AND
    T2.AMENDT > T1.AMENDT
WHERE
    T2.WHS IS NULL

В качестве альтернативы, если db2400 поддерживает NOT EXISTS и коррелированные подзапросы:

SELECT
    T1.WHS,
    T1.CUSNO,
    T1.AMENDT
FROM
    MBC6REVQ T1
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            MBC6REVQ T2
        WHERE
            T2.WHS = T1.WHS AND
            T2.AMENDT > T1.AMENDT
    )

В любом случае вам также необходимо решить, как вы хотите обрабатывать связи и код для этого.