How to get the latest value?-CodePudding

I am trying to write a query and I want to get only the max(date), and I have to group by the rest of info such as job_ID, invoice, total_paid, and payment_method.

I can't group by the payment method because logically its not correct and also because the payment methods are different... and, I cannot use listagg here.

Any idea how can I accomplish my goal? I almost reached the end but the payment method cause some errors in the code...

Thank you in advance :)

CodePudding user response：

You can get the row with the max date for any id with a qualify row_number() over():

with data as (
    select $1 id, $2 date, $3 value
    from values (1, 1, 'a')
        , (1, 2, 'b')
        , (1, 3, 'c')
        , (2, 1, 'e')
        , (2, 2, 'f')
        , (2, 3, 'g')
)

select *
from data
qualify row_number() over(partition by id order by date desc) = 1
;

If this is not what you want — you will need to improve the question by detailing sample input data and desired results.