Skip to Main Content
  • Questions

  • Difference between Lag and First_value

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Shriram.

Asked: December 14, 2017 - 12:38 pm UTC

Last updated: December 19, 2017 - 4:05 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

I just want to know the difference between the below two queries.

Lets say i have a table called member :

SQL> select member_id,member_first_name from member;

MEMBER_ID MEMBER_FIR
---------- ----------
1 A
2 B
3 C
4 D
5 E
6 F
7 G

7 rows selected.

Now i have these two queries :

SQL> select member_first_name,first_value(member_id) over (order by member_id rows between 2 preceding and current row) from member;

MEMBER_FIR FIRST_VALUE(MEMBER_ID)OVER(ORDERBYMEMBER_IDROWSBETWEEN2PRECEDINGANDCURRENTROW)
---------- ------------------------------------------------------------------------------
A 1
B 1
C 1
D 2
E 3
F 4
G 5

7 rows selected.

SQL> select member_first_name,lag(member_id,2,1) over(order by member_id) from member;

MEMBER_FIR LAG(MEMBER_ID,2,1)OVER(ORDERBYMEMBER_ID)
---------- ----------------------------------------
A 1
B 1
C 1
D 2
E 3
F 4
G 5

7 rows selected.

Both the above queries return the same result set. Can you please explain me the difference between these two functions.

and Connor said...

first_value is about the size of your *window*, whereas lag is about an offset from the current row.


                  window size
    +-----------------------------------+
    ^             ^current row
    |
    |       n rows
    |      |------|
    |      ^
    |      |
    |      | lag(,n)
    |
    | first_value()


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Shriram D, December 15, 2017 - 7:24 am UTC

Thats correct .. It works on the window .

Thanks for the reply ! Appreciate your help !
Chris Saxon
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
...

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.