Oracle SQL - Nulls and Analytical Functions

  1. HOME
  2. Oracle SQL
  3. Analytic Functions
  4. Analytic Functions

Introduction

The modifier NULLS LAST or NULLS FIRST (which is the default) may be added to any ordering analytic clause.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w  w w  . ja va 2 s.  c om

SQL> create table emp(
  2  empno        Number(3)  NOT NULL,
  3  ename        VARCHAR2(10 BYTE),
  4  hireDate     DATE,
  5  orig_salary  Number(8,2),
  6  curr_salary  Number(8,2),
  7  region       VARCHAR2(1 BYTE)
  8  );
SQL>
SQL> insert into emp values(1,'Alison',DATE '1996-03-21', 45000, NULL,  'E');
SQL> insert into emp values(2,'James',DATE  '1978-12-12', 23000, 32000, 'W');
SQL> insert into emp values(3,'Celia',DATE  '1982-10-24', NULL,  58000, 'E');
SQL> insert into emp values(4,'Robert',DATE '1984-01-15', 31000, NULL,  'W');
SQL> insert into emp values(5,'Linda',DATE  '1987-07-30', NULL,  53000, 'E');
SQL> insert into emp values(6,'David',DATE  '1990-12-31', 78000, NULL,  'W');
SQL> insert into emp values(7,'Jode',DATE  '1996-09-17', 21000, 29000, 'E');
SQL>
SQL> SELECT empno, ename, curr_salary,
  2    ROW_NUMBER() OVER(ORDER BY curr_salary) salary
  3  FROM emp
  4  ORDER BY curr_salary;

     EMPNO ENAME      CURR_SALARY     SALARY
---------- ---------- ----------- ----------
         7 Jode             29000          1
         2 James            32000          2
         5 Linda            53000          3
         3 Celia            58000          4
         6 David                           5
         1 Alison                          6
         4 Robert                          7

7 rows selected.

SQL>
SQL> --In descending order:
SQL>
SQL> SELECT empno, ename, curr_salary,
  2    ROW_NUMBER() OVER(ORDER BY curr_salary desc) salary
  3  FROM emp
  4  ORDER BY curr_salary desc;

     EMPNO ENAME      CURR_SALARY     SALARY
---------- ---------- ----------- ----------
         1 Alison                          1
         4 Robert                          2
         6 David                           3
         3 Celia            58000          4
         5 Linda            53000          5
         2 James            32000          6
         7 Jode             29000          7

7 rows selected.

SQL>

When nulls are present, there is an option to place nulls first or last with the analytical function.

The default is NULLS FIRST.

Demo

SQL>
SQL> SELECT empno, ename, curr_salary,
  2     ROW_NUMBER() OVER(ORDER BY curr_salary NULLS LAST)
  3          salary-- from w  ww.  j a va  2s .c  o  m
  4  FROM emp
  5  ORDER BY curr_salary;

     EMPNO ENAME      CURR_SALARY     SALARY
---------- ---------- ----------- ----------
         7 Jode             29000          1
         2 James            32000          2
         5 Linda            53000          3
         3 Celia            58000          4
         6 David                           5
         1 Alison                          6
         4 Robert                          7

7 rows selected.

SQL>
SQL> SELECT empno, ename, curr_salary,
  2     ROW_NUMBER() OVER(ORDER BY curr_salary NULLS FIRST)
  3          salary
  4  FROM emp
  5  ORDER BY curr_salary ;

     EMPNO ENAME      CURR_SALARY     SALARY
---------- ---------- ----------- ----------
         7 Jode             29000          4
         2 James            32000          5
         5 Linda            53000          6
         3 Celia            58000          7
         4 Robert                          2
         6 David                           3
         1 Alison                          1

7 rows selected.

SQL>

In the case of NULLS LAST, the ROW_NUMBER is reorganized to place the nulls at the end.

Related Topic