Oracle SQL - Using nulls with NTILE function

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

Introduction

Here is an example using the same query on our emp table with nulls: 

SELECT ename, curr_salary sal, 
   ntile(2) OVER(ORDER BY curr_salary desc) n2, 
   ntile(3) OVER(ORDER BY curr_salary desc) n3, 
   ntile(4) OVER(ORDER BY curr_salary desc) n4, 
   ntile(5) OVER(ORDER BY curr_salary desc) n5, 
   ntile(6) OVER(ORDER BY curr_salary desc) n6, 
   ntile(8) OVER(ORDER BY curr_salary desc) n8 
FROM emp;

--And with NULLS LAST: 

SELECT ename, curr_salary sal, 
  ntile(2) OVER(ORDER BY curr_salary desc NULLS LAST) n2, 
  ntile(3) OVER(ORDER BY curr_salary desc NULLS LAST) n3, 
  ntile(4) OVER(ORDER BY curr_salary desc NULLS LAST) n4, 
  ntile(5) OVER(ORDER BY curr_salary desc NULLS LAST) n5, 
  ntile(6) OVER(ORDER BY curr_salary desc NULLS LAST) n6, 
  ntile(8) OVER(ORDER BY curr_salary desc NULLS LAST) n8 
FROM emp;

The nulls are treated like a value for the NTILE and placed either at the beginning (NULLS FIRST, the default) or the end (NULLS LAST).

The percentile algorithm places null values just before or just after the high and low values for the purposes of placing the row into a given percentile.

Nulls can also be handled by either using NVL or excluding nulls from the result set using an appropriate WHERE clause.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   ww w .  ja v a2s .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 ename, curr_salary sal,
  2     ntile(2) OVER(ORDER BY curr_salary desc) n2,
  3     ntile(3) OVER(ORDER BY curr_salary desc) n3,
  4     ntile(4) OVER(ORDER BY curr_salary desc) n4,
  5     ntile(5) OVER(ORDER BY curr_salary desc) n5,
  6     ntile(6) OVER(ORDER BY curr_salary desc) n6,
  7     ntile(8) OVER(ORDER BY curr_salary desc) n8
  8  FROM emp
  9
SQL> --And with NULLS LAST:
SQL>
SQL> SELECT ename, curr_salary sal,
  2    ntile(2) OVER(ORDER BY curr_salary desc NULLS LAST) n2,
  3    ntile(3) OVER(ORDER BY curr_salary desc NULLS LAST) n3,
  4    ntile(4) OVER(ORDER BY curr_salary desc NULLS LAST) n4,
  5    ntile(5) OVER(ORDER BY curr_salary desc NULLS LAST) n5,
  6    ntile(6) OVER(ORDER BY curr_salary desc NULLS LAST) n6,
  7    ntile(8) OVER(ORDER BY curr_salary desc NULLS LAST) n8
  8  FROM emp
  9

Related Topic