---

SELECT
    td.id,
    td.application_termination_contract_id,
    td.number,
    td.year,
    td.version,
    td.canceled,
    td.accepted,
    td.type,
    contract.number AS contract_number
FROM ourpension.year_tax_deduction td
LEFT JOIN ourpension.application_termination_contract atc
    ON atc.id = td.application_termination_contract_id
LEFT JOIN ourpension.application_termination_contract_data atcd
    ON atcd.application_termination_contract_id = atc.id
LEFT JOIN ourpension.sharer sh ON sh.id = atcd.sharer_id
LEFT JOIN ourpension.contract contract ON contract.id = sh.contract_id
WHERE contract.number = 'НОМЕР_ДОГОВОРА'
ORDER BY td.version, td.id;


WITH all_rows AS (
    SELECT
        td.id,
        td.application_termination_contract_id,
        td.year,
        td.version,
        td.canceled,
        td.number
    FROM ourpension.year_tax_deduction td
    WHERE td.type != 'NONE'
      AND td.number = 'СНВ-XXX'  -- подставь СНВ-id заявления нужного человека
),
versions AS (
    SELECT DISTINCT
        application_termination_contract_id,
        version AS cert_version
    FROM all_rows
),
version_rows AS (
    SELECT
        v.application_termination_contract_id,
        v.cert_version,
        ar.id AS row_id,
        ar.year,
        ar.canceled,
        ROW_NUMBER() OVER (
            PARTITION BY v.application_termination_contract_id, v.cert_version
            ORDER BY ar.year DESC, ar.id DESC
        ) AS row_pick,
        ROW_NUMBER() OVER (
            PARTITION BY v.application_termination_contract_id
            ORDER BY v.cert_version DESC
        ) AS rn_version
    FROM versions v
    JOIN all_rows ar
        ON ar.application_termination_contract_id = v.application_termination_contract_id
       AND ar.version <= v.cert_version
       AND (ar.canceled IS NULL OR ar.canceled > v.cert_version)
)
SELECT *
FROM version_rows
ORDER BY cert_version, row_pick;