Oracle SQL - ROW_NUMBER function

Introduction

There are three ranking-type analytical functions that deal with just such a problem as this:

• ROW_NUMBER,
• RANK, and
• DENSE_RANK.

The format of an analytical function is this:

function() OVER(<analytic clause>) 

where <analytic clause> contains ordering, partitioning, windowing, or some combination.

The ROW_NUMBER function with an ordering on salary in descending order looks like this:

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w ww.  j a  v a  2 s .  com

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>
SQL> SELECT empno,
  2         ename,
  3         orig_salary,
  4         ROW_NUMBER() OVER(ORDER BY orig_salary desc) toprank
  5  FROM emp
  6

Analytical functions will generate an ordering by themselves.

The following code includes a final ordering of the result set with an ORDER BY at the end of the query:

Demo

SQL>
SQL> SELECT empno,
  2         ename,-- w  w  w . j a va  2 s . c om
  3         orig_salary,
  4         ROW_NUMBER() OVER(ORDER BY orig_salary desc) toprank
  5  FROM emp
  6  ORDER BY orig_salary desc
  7

The final ORDER BY ensures the ordering of the final display.

To illustrate a different ordering with the use of analytical functions:

Demo

SQL>
SQL> SELECT empno,
  2         ename,-- w ww  .j  a v  a 2 s. co m
  3         orig_salary,
  4         ROW_NUMBER() OVER(ORDER BY orig_salary desc) toprank
  5  FROM emp
  6  ORDER BY ename
  7

• Previous
• Next