Oracle ROW_NUMBER Function

Oracle ROW_NUMBER Function

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 1 min read

In this post, I will explain how to use the Oracle ROW_NUMBER function with syntax and examples.

Description

ROW_NUMBER is an analytic function in Oracle. It assigns a unique number to each row when used as a column in a query, in the ordered sequence of rows specified with the order by clause.

ROW_NUMBER Syntax

ROW_NUMBER ( ) OVER ([query_partition_clause] order_by_clause)

Oracle ROW_NUMBER Function Example

The following row_number example assigns numbers to each row partitioning by the department in order of employee's salary:

SELECT deptno,
ename,
empno,
hiredate,
sal,
ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal)
AS e_id
FROM emp;

Output

DEPTNO     ENAME      EMPNO      HIREDATE  SAL        E_ID
---------- ---------- ---------- --------- ---------- ----------
10         MILLER     7934       23-JAN-82 1300       1
10         CLARK      7782       06-SEP-81 2450       2
10         KING       7839       17-NOV-81 5000       3
20         SMITH      7369       17-DEC-80 800        1
20         ADAMS      7876       23-MAY-87 1100       2
20         JONES      7566       04-FEB-81 2975       3
20         SCOTT      7788       19-APR-87 3000       4
20         FORD       7902       12-MAR-81 3000       5
30         JAMES      7900       12-MAR-81 950        1
30         MARTIN     7654       28-SEP-81 1250       2
30         WARD       7521       22-FEB-81 1250       3
30         TURNER     7844       09-AUG-81 1500       4
30         ALLEN      7499       20-FEB-81 1600       5
30         BLAKE      7698       05-JAN-81 2850       6

14 rows selected.

See also: