Press "Enter" to skip to content

Oracle ROW_NUMBER Function

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:

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *