SQL & PL/SQL

Hi

i have the below table


Drop table table_2
;
CREATE TABLE table_2
( name VARCHAR2 (10)
, Txn_id NUMBER
, Txn_date date
, Status varchar2(10)
, CONSTRAINT key2 UNIQUE (name, txn_id)
);


INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Jason', 112, TO_DATE ('15-Jan-2011', 'DD-Mon-YYYY'), 'failure');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Jason', 786, TO_DATE ('26-Jan-2011', 'DD-Mon-YYYY'), 'success');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Jason', 987, TO_DATE ('27-Jan-2011', 'DD-Mon-YYYY'), 'failure');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Jason', 119, TO_DATE ('28-Jan-2011', 'DD-Mon-YYYY'), 'success');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Allen', 221, TO_DATE ('19-Apr-2011', 'DD-Mon-YYYY'), 'failure');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Allen', 666, TO_DATE ('22-Apr-2011', 'DD-Mon-YYYY'), 'success');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Allen', 229, TO_DATE ('28-Apr-2011', 'DD-Mon-YYYY'), 'failure');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Allen', 999, TO_DATE ('30-Apr-2011', 'DD-Mon-YYYY'), 'success');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Mat', 559, TO_DATE ('15-Jul-2011', 'DD-Mon-YYYY'), 'success');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Mat', 101, TO_DATE ('20-Jul-2011', 'DD-Mon-YYYY'), 'success');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Mat', 876, TO_DATE ('25-Jul-2011', 'DD-Mon-YYYY'), 'failure');
INSERT INTO table_2 (name, txn_id, txn_date, status) VALUES ('Mat', 127, TO_DATE ('26-Jul-2011', 'DD-Mon-YYYY'), 'failure');


am trying to run the below SQL to understand the difference between FIRST_VALUE and LAST_VALUE

SELECT Name
, Txn_id
, Txn_date
, status
, FIRST_VALUE (status) OVER ( PARTITION BY name ORDER BY txn_date ASC) AS FIRST_val_asc
, FIRST_VALUE (status) OVER ( PARTITION BY name ORDER BY txn_date DESC) AS FIRST_val_des
, LAST_VALUE (status) OVER ( PARTITION BY name ORDER BY txn_date ASC) AS last_val_asc
, LAST_VALUE (status) OVER ( PARTITION BY name ORDER BY txn_date DESC) AS last_val_des
FROM table_2
;

The resultset is as below

NAME TXN_ID TXN_DATE STATUS FIRST_VAL_ASC FIRST_VAL_DES LAST_VAL_ASC LAST_VAL_DES
Jason 112 15-JAN-11 failure failure success failure failure
Jason 786 26-JAN-11 success failure success success success
Jason 987 27-JAN-11 failure failure success failure failure
Jason 119 28-JAN-11 success failure success success success
Allen 221 19-APR-11 failure failure success failure failure
Allen 666 22-APR-11 success failure success success success
Allen 229 28-APR-11 failure failure success failure failure
Allen 999 30-APR-11 success failure success success success
Mat 559 15-JUL-11 success success failure failure failure
Mat 101 20-JUL-11 success success failure success success
Mat 876 25-JUL-11 failure success failure failure failure
Mat 127 26-JUL-11 failure success failure failure failure

am able to understand the working and practical use of FIRST_VALUE function. It sets the final status of the record set of a customer, based on FIRST transaction status (with option ASC) or last transaction status (with option DESC).


But need some help to understand how the LAST_VALUE function works or practical cases where it could be used


Regards
-Learnsequel