Последняя запись по каждому col2

48. Получить последнюю запись для каждого col2

Условие задачи:
Дана таблица table1:

col1 — первичный ключ;
col2 — неуникальный идентификатор, связанный с другой таблицей;
col3 — сумма;
col4 — дата изменения записи.

Необходимо для каждого уникального значения col2 вернуть только одну самую свежую запись по col4.

Код:

table1

col1 — primary key
col2 — secondary / foreign key
col3 — amount
col4 — change date

Спойлеры к решению

Подсказки

💡 Раздели строки на группы по col2.
💡 Внутри каждой группы отсортируй записи по col4 DESC.
💡 Присвой строкам номера через ROW_NUMBER().
💡 Затем оставь только строки с номером 1.
💡 Если даты совпадают, добавь дополнительную сортировку по первичному ключу для однозначного результата.

Решение

Универсальный вариант через оконную функцию:

SELECT
    col1,
    col2,
    col3,
    col4
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY col2
            ORDER BY col4 DESC, col1 DESC
        ) AS rn
    FROM table1 t
) t
WHERE rn = 1;

PARTITION BY col2 создаёт отдельную группу для каждого значения col2.

ORDER BY col4 DESC, col1 DESC

ставит самую свежую запись первой. col1 используется как дополнительный критерий, если несколько записей имеют одинаковую дату.

После этого:

WHERE rn = 1

оставляет ровно одну запись для каждого col2.

Для PostgreSQL можно использовать более короткий вариант:

SELECT DISTINCT ON (col2)
    col1,
    col2,
    col3,
    col4
FROM table1
ORDER BY
    col2,
    col4 DESC,
    col1 DESC;

Для ускорения такого запроса в PostgreSQL можно создать индекс:

CREATE INDEX idx_table1_col2_col4
    ON table1 (col2, col4 DESC, col1 DESC);