Oracle SQL - Nulls and Analytical 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.--   w w  w .  j a v  a2 s  . co m

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--  w  ww . j a v  a2  s  . co 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