7 Replies Latest reply: Apr 23, 2013 2:20 AM by 1004795 RSS

    FIRST_VALUE() and LAST_VALUE Analytic Functions

    sathya_mounika
      Hi All,

      May be this is a basic question. But I am having a hard time understanding the difference between FIRST_VALUE() and LAST_VALUE() Analytical Functions.

      As far as what I have read FIRST_VALUE function picks up the first record after the partition and order by and returns it after any computation. And the LAST_VALUE does the opposite. But the result of the second query what I expected did not show up (last value of the partition). It would be of some help if anyone could throw some light on it.
      select empno
           , ename
           , sal
           , first_value(ename) over(order by sal desc)
        from emp;
      
      empno  ename      sal       first_value 
      ------ ---------- --------- ----------- 
      7839   KING       5000.00   KING        
      7902   FORD       3000.00   KING        
      7788   SCOTT      3000.00   KING        
      7566   JONES      2975.00   KING        
      7698   BLAKE      2850.00   KING        
      7782   CLARK      2450.00   KING        
      7499   ALLEN      1600.00   KING        
      7844   TURNER     1500.00   KING        
      7934   MILLER     1300.00   KING        
      7654   MARTIN     1250.00   KING        
      7521   WARD       1250.00   KING        
      7876   ADAMS      1100.00   KING        
      7900   JAMES      950.00    KING        
      7369   SMITH      800.00    KING        
      
      14 Row(s) affected
      select empno
           , ename
           , sal
           , last_value(ename) over(order by sal desc)
        from emp;
      
      empno  ename      sal       last_value 
      ------ ---------- --------- ---------- 
      7839   KING       5000.00   KING       
      7902   FORD       3000.00   SCOTT      
      7788   SCOTT      3000.00   SCOTT      
      7566   JONES      2975.00   JONES      
      7698   BLAKE      2850.00   BLAKE      
      7782   CLARK      2450.00   CLARK      
      7499   ALLEN      1600.00   ALLEN      
      7844   TURNER     1500.00   TURNER     
      7934   MILLER     1300.00   MILLER     
      7521   WARD       1250.00   MARTIN     
      7654   MARTIN     1250.00   MARTIN     
      7876   ADAMS      1100.00   ADAMS      
      7900   JAMES      950.00    JAMES      
      7369   SMITH      800.00    SMITH      
      
      14 Row(s) affected
      Thanks
      Venkat
        • 1. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
          Frank Kulash
          Hi, Venkat,

          When you use an analytic function with an ORDER BY clause, the results will be based on a window, which is a subset of the partition.
          If you don't specify a window (by using the RANGE or ROWS keywords) the window will include everything in order by the ORDER BY clause up to and including the current row, including ties.

          For example, in your second query:
          select empno
               , ename
               , sal
               , last_value(ename) over(order by sal desc)
            from emp;
           
          empno  ename      sal       last_value 
          ------ ---------- --------- ---------- 
          7839   KING       5000.00   KING       
          7902   FORD       3000.00   SCOTT      
          7788   SCOTT      3000.00   SCOTT      
          7566   JONES      2975.00   JONES      
          7698   BLAKE      2850.00   BLAKE      
          7782   CLARK      2450.00   CLARK      
          7499   ALLEN      1600.00   ALLEN      
          7844   TURNER     1500.00   TURNER     
          7934   MILLER     1300.00   MILLER     
          7521   WARD       1250.00   MARTIN     
          7654   MARTIN     1250.00   MARTIN     
          7876   ADAMS      1100.00   ADAMS      
          7900   JAMES      950.00    JAMES      
          7369   SMITH      800.00    SMITH    
          The analytic function
          last_value(ename) over(order by sal desc)
          will return the last ename, not of the entire table, but of the window starting with the highest sal (since you're saying "ORDER BY sal DESC"), and including the current row, and any other rows that happen to have the same sal.

          So consider the 1st row, with ename='KING'. It has the higest sal, so there is only one row in the window, and 'KING' IS THE LAST VALUE IN THAT WINDOW.

          Now consider the 2nd row, where ename='FORD' and sal=3000. The window will now include everybody with a sal of 3000 and higher, whihc means the 3 rows 'KING', 'FORD' and 'SCOTT'. The last of these (in descending order by sal) is 'SCOTT'. (Actually, there's a tie; you could equally well say that 'JONES' is last, since there's a tie between the two rows where sal=3000. When that happens, one of the rows will arbitrarily be designated the "last" row. don't expect it to always be the same row.)

          Becuase of this, LAST_VALUE is alwmost always used with an explicit windowing clause, starting with RANGE or ROWS.

          If you want a query like your first query, but it includes the name of the lowest paid employee (that is, it always says 'SMITH' in the last column instead of 'KING'), then use FIRST_VALUE, but reverse the sorting order:
          first_value(ename) over(order by sal ASC)
          • 2. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
            229023
            You may have to specify the clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Results are unchanged even if you use this clause for first_value function. I'm assuming the analytical function takes into account only the current row and the preceding row.
            hr@orcl> select employee_id, first_name, last_name, salary, last_value(first_name) over
              2  (order by salary desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
              3  from employees;
                    100 Steven               King                      24000 TJ
                    101 Neena                Kochhar                   17000 TJ
                    102 Lex                  De Haan                   17000 TJ
                    145 John                 Russell                   14000 TJ
                    146 Karen                Partners                  13500 TJ
                    201 Michael              Hartstein                 13000 TJ
                    205 Shelley              Higgins                   12000 TJ
                    108 Nancy                Greenberg                 12000 TJ
                    147 Alberto              Errazuriz                 12000 TJ
                    168 Lisa                 Ozer                      11500 TJ
                    148 Gerald               Cambrault                 11000 TJ
                    174 Ellen                Abel                      11000 TJ
                    114 Den                  Raphaely                  11000 TJ
                    162 Clara                Vishney                   10500 TJ
                    149 Eleni                Zlotkey                   10500 TJ
                    169 Harrison             Bloom                     10000 TJ
                    ..................
                    119 Karen                Colmenares                 2500 TJ
                    182 Martha               Sullivan                   2500 TJ
                    131 James                Marlow                     2500 TJ
                    144 Peter                Vargas                     2500 TJ
                    191 Randall              Perkins                    2500 TJ
                    127 James                Landry                     2400 TJ
                    135 Ki                   Gee                        2400 TJ
                    128 Steven               Markle                     2200 TJ
                    136 Hazel                Philtanker                 2200 TJ
                    132 TJ                   Olson                      2100 TJ
            • 3. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
              MaximDemenko
              Sundar M wrote:
              I'm assuming the analytical function takes into account only the current row and the preceding row.
              Analytical functions are calculated based on windows which defaults to rows between unbounded preceding and current row when not specified otherwise.

              Best regards

              Maxim
              • 4. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
                sathya_mounika
                Thanks a lot guys. That was very elaborate.
                • 5. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
                  1004795
                  1* select ename,deptno,sal,first_value(sal) over(),last_value(sal) over() from emp
                  SQL> /

                  ENAME DEPTNO SAL FIRST_VALUE LAST_VALUE
                  ---------- ---------- ---------- ---------------------- ---------------------
                  KING 10 5000 5000 1300

                  BLAKE 30 2850 5000 1300

                  CLARK 10 2450 5000 1300

                  JONES 20 2975 5000 1300

                  MARTIN 30 1250 5000 1300

                  ALLEN 30 1600 5000 1300

                  TURNER 30 1500 5000 1300

                  JAMES 30 950 5000 1300

                  WARD 30 1250 5000 1300

                  FORD 20 3000 5000 1300

                  SMITH 20 800 5000 1300

                  SCOTT 20 3000 5000 1300

                  ADAMS 20 1100 5000 1300

                  MILLER 10 1300 5000 1300


                  14 rows selected.




                  SQL> ed
                  Wrote file afiedt.buf

                  1 select ename,deptno,sal,
                  2 first_value(sal)
                  3 over(partition by deptno) firstvalue,
                  4 last_value(sal)
                  5* over(partition by deptno) lastvalue from emp
                  SQL> /

                  ENAME DEPTNO SAL FIRSTVALUE LASTVALUE
                  ---------- ---------- ---------- ---------- ----------

                  KING 10 5000 5000 1300

                  CLARK 10 2450 5000 1300

                  MILLER 10 1300 5000 1300

                  ADAMS 20 1100 1100 2975

                  SCOTT 20 3000 1100 2975

                  SMITH 20 800 1100 2975

                  FORD 20 3000 1100 2975

                  JONES 20 2975 1100 2975

                  WARD 30 1250 1250 1500

                  JAMES 30 950 1250 1500

                  ALLEN 30 1600 1250 1500

                  MARTIN 30 1250 1250 1500

                  BLAKE 30 2850 1250 1500

                  TURNER 30 1500 1250 1500


                  14 rows selected.

                  Edited by: 1001792 on Apr 23, 2013 12:05 AM
                  • 6. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
                    jeneesh
                    Welcome to the forum..

                    Please dont dig out years old threads, which are already marked as answered..
                    • 7. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
                      1004795
                      1 select ename,
                      2 deptno,
                      3 sal,
                      4 first_value(sal) over() firstvalue,
                      5* last_value(sal) over() lastvalue from emp





                      1 select ename,deptno,sal,
                      2 first_value(sal)
                      3 over(partition by deptno) firstvalue,
                      4 last_value(sal)
                      5* over(partition by deptno) lastvalue from emp