Получить дату последнего изменения на основе изменения значения столбца в SQL

У нас есть таблица Transmission_Status:

Нам нужна дата последнего изменения, когда изменяется столбец NEW_VALUE (в данном случае, изменился с Неактивно на Завершено).

Текущий запрос:

select 
    key_value, field, new_value, date_modified
from
    (select 
         key_value, field, new_value, date_modified,
         rank() over (partition by key_value order by date_modified desc) rnk
     from 
         transmission_status
     where 
         key_value = 45 
         and Field = 'Статус передачи'
) x
where 
    rnk = 1

Текущий результат = Date_Modified = 11/07/2024

Ожидаемый результат = Date_Modified = 15/05/2024 (2-я строка)

Как я могу исправить это, чтобы получить ожидаемый результат?

Нам нужна дата последнего изменения всякий раз, когда изменяется столбец NEW_VALUE

Мне потребовалось некоторое время, чтобы понять это, сопоставляя только запрос на 2-ю строку вместо строк 2 и 4. Но я думаю, что теперь я понял, и более точное формулирование задачи выглядит так:

Нам нужна самая ранняя дата изменения из финального/конечного статуса NEW_VALUE.

Во-первых, если вы хотите узнать, когда значение меняется от одной строки к следующей, вместо rank() я бы предпочел использовать lead() или lag(), чтобы посмотреть на соседнюю строку.

Затем нам нужно узнать первую запись в каждом изменении. К сожалению, я не смог сделать это без еще одного уровня вложенности. Но мне удалось придумать следующее:

WITH Priors AS (
    select key_value, field, new_value, date_modified
        ,lag(new_value) over (partition by key_value, field order by date_modified) prior_value

    from Transmission_Status
    where key_value = 45 and Field ='Статус передачи'
), numberedChanges AS (
    select key_value, field, new_value, date_modified
      ,row_number() over (order by date_modified desc) rn
    from Priors
    where new_value <> prior_value
)
SELECT Key_Value, FIELD, New_Value, Date_Modified
FROM numberedChanges
WHERE rn = 1

https://dbfiddle.uk/8EuFC4Xc

Неудача в том, чтобы иметь только один вложенный запрос, заключалась в невозможности получить одинаковый номер rank() для каждого набора записей New_Value в рамках дальнейшей разбивки по key_value/field на основе минимальной даты. Если бы я мог найти способ сделать это, я мог бы также добавить row_number() в эту разбивку и искать строки (строку), где и rank, и номер равны 1.

Но то, что у меня есть, работает так, как есть.