SQL: удалить все записи NOT MAX в GroupBy

Моя цель - удалить все записи из моей таблицы, которые НЕ являются MAX (recordDate) сгруппированного CaseKey. Поэтому, если у меня есть 9 записей с 3 наборами из 3 ключевых слов, и каждый casekey имеет три даты. Я бы удалил две младшие даты каждого набора и придумал 3 общей записи, только MAX (recordDate) каждого из оставшихся.

У меня есть следующий SQL-запрос:

    DELETE FROM table
    WHERE tableID NOT IN (
    SELECT tableID
    FROM (
    Select MAX(recordDate) As myDate, tableID From table
    Group By CaseKey
    ) As foo
    )

Я получаю сообщение об ошибке: Ошибка в строке 3... Столбец "table.tableID" недопустим в списке выбора, потому что он не содержится ни в агрегатной функции, ни в предложении GROUP BY.

Очевидно, я мог бы добавить tableID в мое предложение Group By, но тогда результат этого утверждения неверен и возвращает все строки вместо того, чтобы просто возвращать MAX recordDate из сгруппированных CaseKeys.

Сейчас сервер выключен, но кажущийся ответ: (крошечная настройка из ответа WildPlasser)

DELETE zt FROM ztable zt
WHERE EXISTS (
    SELECT * FROM ztable ex
    WHERE ex.CaseKey = zt.CaseKey
    AND ex.recordDate > zt.recordDate
);

Другими словами, для каждой записи в zt запустите запрос, чтобы увидеть, имеет ли эта же запись запись с более высоким значением recordDate. Если это так, выполняется инструкция WHERE EXISTS и запись удаляется, в противном случае оператор WHERE терпит неудачу, а запись - это собственный MAX recordDate.

Спасибо, WildPlasser, за эту упрощенную методологию, которую я как-то взорвал.

Ответ 1

Существует одно специальное свойство MAX: нет записи с более высоким значением, чем max. Таким образом, мы можем удалить все записи, для которых существует запись с одним и тем же CaseKey, но с более высоким значением записи:

DELETE FROM ztable zt
WHERE EXISTS (
    SELECT *
    FROM ztable ex
    WHERE ex.CaseKey = zt.CaseKey
    AND ex.recordDate > zt.recordDate
    );

BTW: вышеупомянутый запрос (а также версия MAX()) предполагает, что существует только одна запись с максимальной датой. Могут быть связи.

В случае связей вам нужно добавить дополнительное поле в предложение where; как тай-брейк. Предполагая, что TableId может функционировать как таковой, запрос будет выглядеть следующим образом:

DELETE FROM ztable zt
WHERE EXISTS (
    SELECT *
    FROM ztable ex
    WHERE ex.CaseKey = zt.CaseKey
    AND (   ex.recordDate > zt.recordDate
        OR (ex.recordDate = zt.recordDate AND ex.TableId > zt.TableId)
        )
    );

Ответ 2

Просто выразить

удалить все записи из моей таблицы, которые НЕ являются MAX (recordDate) сгруппированный CaseKey

в sql как

DELETE FROM table t1
WHERE t1.recordDate <> 
  (SELECT MAX(recordDate)
   FROM table t2
   WHERE t2.CaseKey = t1.CaseKey)

Ответ 3

Вы можете ранга записывать все записи с тем же caseKey, где ранг > 1 возвращает только нижние даты. Таким образом, вы можете использовать tableID.

DELETE FROM [table]
WHERE [tableID] IN
  (SELECT
    [sub].[tableID]
  FROM
  (
    SELECT 
      [tableID],
      Rank() OVER (PARTITION BY [caseKey] ORDER BY [recordDate] DESC, [tableID] DESC) AS [rank]
    FROM [table]
  ) AS [sub]
  WHERE [sub].[rank] > 1)