Too many first_value in query?

Question

I want to get the first invoice by its date of every po_distribution_id and its data:

SELECT UNIQUE 
first_value(i.invoice_id) OVER (PARTITION BY po_distribution_id ORDER BY i.invoice_date asc) invoice_id
,first_value(i.invoice_date) OVER (PARTITION BY po_distribution_id ORDER BY i.invoice_date asc) invoice_date
,first_value(i.invoice_num) OVER (PARTITION BY po_distribution_id ORDER BY i.invoice_date asc) invoice_date
FROM AP_INVOICE_DISTRIBUTIONS_ALL d LEFT JOIN ap_invoices_all i on d.invoice_id = i.invoice_id
WHERE sysdate - i.invoice_date < 30
ORDER BY invoice_id DESC

Wonder if there is a better way of accomplishing this without using so many first_value. The query is not slow, but I feel uneasy with it.

Example:

po_distribution_id  invoice_id  invoice_date
212121              2212        2019-05-05
212121              2355        2019-01-09
212121              1122        2019-02-02
212121              7744        2019-03-04

Should return:

po_distribution_id  invoice_id  invoice_date
212121              2355        2019-01-09

Answer 1

All three first_value() functions use the same partition but the last one has a different ordering (invoice_date, vs invoice_num in the first two). If the ordering of invoice_num and invoice_date are the same, then you can use row_number() in a subquery, and filter in the outer query:

select *
from (
    select 
        i.invoice_id,
        i.invoice_date,
        i.invoice_num,
        row_number() over(partition by po_distribution_id order by i.invoice_num) rn
    from 
        ap_invoice_distributions_all d 
        left join ap_invoices_all i on d.invoice_id = i.invoice_id
    where sysdate - i.invoice_date < 30
) x
where rn = 1