D
D
Dmitry Mityaev2019-09-20 12:45:01
Oracle
Dmitry Mityaev, 2019-09-20 12:45:01

PL SQL: Why does FIRST VALUE with DESC sort and LAST_VALUE produce different values?

Essence of the question. I saw the FIRST_VALUE function with reverse sorting by DESC in the colleagues' script.
I need to rewrite it for another DBMS that does not understand DESC
SELECT distinct account_id,
FIRST_VALUE(amount)over(PARTITION BY account_id ORDER BY payment_date desc)last_payment
FROM kollecto.payments
where account_id in (759318,759320,759322,759324)
order by account_id ;
The question immediately arose: why not use LAST_VALUE with default sorting?
Tried:
SELECT distinct account_id,
LAST_VALUE(amount)over(PARTITION BY account_id ORDER BY payment_date)last_payment
FROM kollecto.payments
where account_id in (759318,759320,759322,759324)
order by account_id;
But the problem is that it returned a very different set of values.
What's the catch here?5d849f92489bf475382460.jpeg
5d849f9a20e3c098472552.jpeg

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question