NULLS LAST: place null value at the end : NULLs Last « Analytical Functions « Oracle PL / SQL






NULLS LAST: place null value at the end



SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /

Table created.

SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                   orig_salary, curr_salary, region)
  2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       NULL,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,'Celia',to_date('19821024','YYYYMMDD'), NULL,       58000,        'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      NULL,        'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,'Linda', to_date('19870730','YYYYMMDD'), NULL,       53000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       NULL,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       29000,       'E')
  3  /

1 row created.

SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue

     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000             E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82                   58000 E
       105 Robert     15-JAN-84       31000             W
       116 Linda      30-JUL-87                   53000 E
       117 David      31-DEC-90       78000             W
       108 Jode       17-SEP-96       21000       29000 E

7 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> -- When nulls are present, there is an option to place nulls first or last with the analytical function.
SQL>
SQL> SELECT empno, ename, curr_salary,
  2    ROW_NUMBER() OVER(ORDER BY curr_salary NULLS LAST)
  3       salary
  4  FROM employee
  5  ORDER BY curr_salary;
Hit a key to continue

     EMPNO ENAME      CURR_SALARY     SALARY
---------- ---------- ----------- ----------
       108 Jode             29000          1
       123 James            32000          2
       116 Linda            53000          3
       104 Celia            58000          4
       117 David                           5
       122 Alison                          6
       105 Robert                          7

7 rows selected.

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;

Table dropped.

SQL>
SQL>
           
       








Related examples in the same category

1.Control whether nulls are the highest or lowest in a group using NULLS LAST
2.If NULLS LAST is left out of the final ORDER BY, the effect will be lost
3.If the statement were without NULLS LAST, the values of the NVL'd nulls occur first
4.order by nulls last