Как использовать полученное значение из подзапроса для join?

Question

[uRoot]

У меня есть подзапрос:

select (
 select json_build_object('id', apa.id, 'status', apa.status)
 from cc.additional_projects_actions apa
 where apa.order_id = va.order_id 
  and apa.sale_type_id = va.sale_type_id
 order by apa.created_at 
 desc limit 1
) as id_and_status
from vt.applications va
join orders o on va.order_id = o.id 
where 
 o.id = 64
 and va.sale_type_id is not null
order by va.created_at desc;

Как мне его переписать, чтобы я мог взять данные с id_and_status.id и использовать их для left join с таблицей сс.second_table?

Использовать просто left join типа этого я не могу:
join orders o on сс.second_table.id = apa.id
У меня дубли в таблице.

Comments

[Akina]

Как мне его переписать, чтобы я мог взять данные с id_and_status.id и использовать их для left join с таблицей сс.second_table?

Переместить подзапрос в FROM. С соответствующей корректировкой, ясное дело.

Answer 1

[alexalexes]

Если я не напутал с окном partition by va.order_id, apa.sale_type_id, то скорее всего можно переписать так:

select json_build_object('id', A.id, 'status', A.status)
from (select apa.id,  apa.status, va.created_at,
             row_number() over (partition by va.order_id, apa.sale_type_id order by apa.created_at desc) Rn 
          from vt.applications va
          join orders o on va.order_id = o.id 
          join cc.additional_projects_actions apa on apa.order_id = va.order_id
          and apa.sale_type_id = va.sale_type_id
          where o.id = 64
          and va.sale_type_id is not null
) A
where A.Rn = 1 -- отсекаем 1 запись - аналог order by apa.created_at desc limit 1 из первоначального запроса
order by A.created_at desc;

Comments

[uRoot]

Класс, спасибо! ChatGPT предложит такое решение:

SELECT 
    apa.sale_type_id,
    apa.status
FROM vt.applications a
JOIN orders o ON a.order_id = o.id 
LEFT JOIN (
    SELECT 
        apa.order_id,
        apa.sale_type_id,
        apa.status,
        ROW_NUMBER() OVER (PARTITION BY apa.order_id, apa.sale_type_id ORDER BY apa.created_at DESC) AS rn
    FROM cc.additional_projects_actions apa
) apa ON apa.order_id = a.order_id AND apa.sale_type_id = a.sale_type_id AND apa.rn = 1
WHERE 
    o.id = 64
    AND a.sale_type_id IS NOT NULL
ORDER BY a.created_at DESC;

То же самое решение