Shriram D, December 15, 2017 - 7:24 am UTC
Thats correct .. It works on the window .
Thanks for the reply ! Appreciate your help !
December 19, 2017 - 4:05 pm UTC
Glad this helped
Mimic each with other
Duke Ganote, January 10, 2018 - 5:05 pm UTC
I found it an interesting, learning exercise to mimic LAG with FIRST_VALUE, and vice versa:
WITH e AS (
SELECT emp.*
, ROW_NUMBER()OVER(ORDER BY empno) r#
FROM Scott.emp
)
SELECT empno
-- ---------------------------------------
-- demonstrate LAG mimicked by FIRST_VALUE
-- ---------------------------------------
, LAG(empno,2)OVER(ORDER BY empno) lag2
, CASE WHEN r# > 2 THEN
FIRST_VALUE(empno)OVER(ORDER BY empno
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )
END AS FV_AS_LAG2
-- ---------------------------------------
-- demonstrate FIRST_VALUE mimicked by LAG
-- ---------------------------------------
, FIRST_VALUE(empno)OVER(ORDER BY empno) FV
, LAG(empno,r#-1)OVER(ORDER BY empno) LAG_AS_FV
FROM e
ORDER BY 1;
EMPNO LAG2 FV_AS_LAG2 FV LAG_AS_FV
7369 - - 7369 7369
7499 - - 7369 7369
7521 7369 7369 7369 7369
7566 7499 7499 7369 7369
7654 7521 7521 7369 7369
7698 7566 7566 7369 7369
7782 7654 7654 7369 7369
...