8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

FIRST_VALUE and LAST_VALUE Analytic Functions

This article gives an overview of the FIRST_VALUE and LAST_VALUE analytic functions. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Related articles.

Setup

The examples in this article require the following table.

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

FIRST_VALUE

The basic description for the FIRST_VALUE analytic function is shown below. The analytic clause is described in more detail here.

FIRST_VALUE 
  { (expr) [ {RESPECT | IGNORE} NULLS ]
  | (expr [ {RESPECT | IGNORE} NULLS ])
  }
  OVER (analytic_clause)

The FIRST_VALUE analytic function is similar to the FIRST analytic function, allowing you to return the first result from an ordered set.

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal) IGNORE NULLS 
         OVER (PARTITION BY deptno ORDER BY sal) AS lowest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL LOWEST_IN_DEPT
---------- ---------- ---------- --------------
      7934         10       1300           1300
      7782         10       2450           1300
      7839         10       5000           1300
      7369         20        800            800
      7876         20       1100            800
      7566         20       2975            800
      7788         20       3000            800
      7902         20       3000            800
      7900         30        950            950
      7654         30       1250            950
      7521         30       1250            950
      7844         30       1500            950
      7499         30       1600            950
      7698         30       2850            950

SQL>

The "{RESPECT | IGNORE} NULLS" clause indicates if NULLs are considered when determining results.

The windowing clause can be used to alter the window of operation. The following example uses "ROWS 1 PRECEDING" to give a result similar, but not quite the same, to a LAG of 1 row.

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal ROWS 1 PRECEDING) AS preceding_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL PRECEDING_IN_DEPT
---------- ---------- ---------- -----------------
      7934         10       1300              1300
      7782         10       2450              1300
      7839         10       5000              2450
      7369         20        800               800
      7876         20       1100               800
      7566         20       2975              1100
      7788         20       3000              2975
      7902         20       3000              3000
      7900         30        950               950
      7654         30       1250               950
      7521         30       1250              1250
      7844         30       1500              1250
      7499         30       1600              1500
      7698         30       2850              1600

SQL>

LAST_VALUE

The basic description for the LAST_VALUE analytic function is shown below. The analytic clause is described in more detail here.

LAST_VALUE
  { (expr) [ { RESPECT | IGNORE } NULLS ]
  | (expr [ { RESPECT | IGNORE } NULLS ])
  OVER (analytic_clause)

The LAST_VALUE analytic function is similar to the LAST analytic function, allowing you to return the last result from an ordered set. Using the default windowing clause the result can be a little unexpected.

SELECT empno,
       deptno,
       sal,
       LAST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal) AS highest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL HIGHEST_IN_DEPT
---------- ---------- ---------- ---------------
      7934         10       1300            1300
      7782         10       2450            2450
      7839         10       5000            5000
      7369         20        800             800
      7876         20       1100            1100
      7566         20       2975            2975
      7788         20       3000            3000
      7902         20       3000            3000
      7900         30        950             950
      7654         30       1250            1250
      7521         30       1250            1250
      7844         30       1500            1500
      7499         30       1600            1600
      7698         30       2850            2850

SQL>

This is because the default windowing clause is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", which in this example means the first row with the same value as that of the current row will always be the last row considered. Altering the windowing clause to "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" gives us the result we probably expected.

SELECT empno,
       deptno,
       sal,
       LAST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN
           UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL HIGHEST_IN_DEPT
---------- ---------- ---------- ---------------
      7934         10       1300            5000
      7782         10       2450            5000
      7839         10       5000            5000
      7369         20        800            3000
      7876         20       1100            3000
      7566         20       2975            3000
      7788         20       3000            3000
      7902         20       3000            3000
      7900         30        950            2850
      7654         30       1250            2850
      7521         30       1250            2850
      7844         30       1500            2850
      7499         30       1600            2850
      7698         30       2850            2850

SQL>

As with the previous function, the "{RESPECT | IGNORE} NULLS" clause indicates if NULLs are considered when determining results. The default action is RESPECT NULLS.

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause.

AVG * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION_DETAILS
PREDICTION_PROBABILITY PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions *
ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM *
VAR_POP * VAR_SAMP * VARIANCE * String Aggregation Top-N Queries

For more information see:

Hope this helps. Regards Tim...

Back to the Top.