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

Home » Articles » Misc » Here

FIRST and LAST Analytic Functions

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

Most of the time I find myself using FIRST_VALUE and LAST_VALUE Analytic Functions in preference to FIRST and LAST. Pick which feels best for your use case.

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 Analytic Function

The FIRST analytic function can be used to return the first value from an ordered sequence. Say we want to display the salary of each employee, along with the lowest salary within their department we may use something like.

select empno,
       deptno,
       sal,
       min(sal) keep (dense_rank first order by sal) over (partition by deptno) as lowest
from   emp
order by deptno, sal;

     EMPNO     DEPTNO        SAL     LOWEST
---------- ---------- ---------- ----------
      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 MIN function is almost irrelevant here as it's FIRST and KEEP that are picking the row whose value will be used. We can demonstrate this by using MAX for the low value.

select empno,
       deptno,
       sal,
       max(sal) keep (dense_rank first order by sal) over (partition by deptno) as lowest
from   emp
order by deptno, sal;

     EMPNO     DEPTNO        SAL     LOWEST
---------- ---------- ---------- ----------
      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>

We get the same result.

We could also achieve the same result using FIRST_VALUE or MIN as basic analytic functions. In practice I don't use FIRST very often.

LAST Analytic Function

The LAST analytic function can be used to return the last value from an ordered sequence. Say we want to display the salary of each employee, along with the highest within their department we may use something like.

select empno,
       deptno,
       sal,
       max(sal) keep (dense_rank last order by sal) over (partition by deptno) as highest
from   emp
order by deptno, sal;

     EMPNO     DEPTNO        SAL    HIGHEST
---------- ---------- ---------- ----------
      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>

The MAX function is almost irrelevant here as it's LAST and KEEP that are picking the row whose value will be used. We can demonstrate this by using MIN for the high value.

select empno,
       deptno,
       sal,
       min(sal) keep (dense_rank last order by sal) over (partition by deptno) as highest
from   emp
order by deptno, sal;

     EMPNO     DEPTNO        SAL    HIGHEST
---------- ---------- ---------- ----------
      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>

We get the same result.

We could also achieve the same result using LAST_VALUE or MAX as basic analytic functions. In practice I don't use LAST very often.

Quick Links

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

AVG * BIT_AND_AGG * BIT_OR_AGG * BIT_XOR_AGG * CHECKSUM *
CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_SET CORR *
COUNT * COVAR_POP * COVAR_SAMP * CUME_DIST DENSE_RANK
FEATURE_DETAILS FEATURE_ID FEATURE_SET FEATURE_VALUE FIRST
FIRST_VALUE * KURTOSIS_POP * KURTOSIS_SAMP * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION
PREDICTION_COST PREDICTION_DETAILS PREDICTION_PROBABILITY PREDICTION_SET RANK
RATIO_TO_REPORT REGR_ (Linear Regression) Functions * ROW_NUMBER SKEWNESS_POP * SKEWNESS_SAMP *
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.