Oracle Add Row Number

In this Oracle tutorial, we will learn how to add row numbers to the oracle database. We are working on Oracle database 19c but the steps shared in this tutorial will work on all the versions of the oracle database.

• What is Add Row Number in Oracle database
• Oracle how to add row number
• Oracle add row number column
• Oracle SQL developer add row number
• Oracle add row_number to select statement

Run the below script to create a table with records. Moving forward, we will use this table in our examples to demonstrate the working of Row Number in oracle database 19c & 21c.

  CREATE TABLE "SYS"."HOSPITALS_IN_USA" 
   (	"ID" NUMBER(38,0), 
	"NAME" VARCHAR2(128 BYTE), 
	"CITY" VARCHAR2(26 BYTE), 
	"STATE" VARCHAR2(26 BYTE), 
	"STATUS" VARCHAR2(26 BYTE), 
	"COUNTRY" VARCHAR2(26 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
REM INSERTING into SYS.HOSPITALS_IN_USA
SET DEFINE OFF;
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684220,'VERMONT PSYCHIATRIC CARE HOSPITAL','BERLIN','VT','OPEN','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684219,'VERITAS COLLABORATIVE NORTH CAROLINA LLC','DURHAM','NC','CLOSED','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684227,'WOMEN''S HOSPITAL','GREENSBORO','NC','OPEN','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684228,'ZACHARY - AMG SPECIALTY HOSPITAL','ZACHARY','LA','OPEN','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684217,'VA MAINE HEALTHCARE SYSTEM TOGUS','AUGUSTA','ME','CLOSED','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684226,'WILMINGTON VA MEDICAL CENTER','WILMINGTON','DE','OPEN','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684222,'WASHINGTON DC VA MEDICAL CENTER','WASHINGTON','DC','CLOSED','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684221,'VETERANS HEALTH CARE SYSTEM OF THE OZARKS','FAYETTEVILLE','AR','OPEN','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684216,'VA EASTERN COLORADO HEALTHCARE SYSTEM','DENVER','CO','OPEN','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684218,'VA NEBRASKA-WESTERN IOWA HEALTH CARE SYSTEM','GRAND ISLAND','NE','CLOSED','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684223,'WESTERN NEW YORK CHILDREN''S PSYCHIATRIC CENTER','WEST SENECA','NY','OPEN','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684224,'WESTERN WISCONSIN HEALTH','BALDIWN','WI','OPEN','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684196,'SPOONER HOSPITAL SYS','SPOONER','WI','CLOSED','USA');
Insert into SYS.HOSPITALS_IN_USA (ID,NAME,CITY,STATE,STATUS,COUNTRY) values (167684225,'WILLOW CREEK BEHAVIORAL HEALTH','GREEN BAY','WI','OPEN','USA');

What is Add Row Number in Oracle database

Row Number is an analytical function that assigns a unique value to each row to which it is applied. It consists of an Over clause within which Partition by and order by clauses are used.

Using the Row Number function, you can get the sequence of each element present within a particular group or region.

For example, The country United States consists of many states and each state has a good hospital to serve patients. There can be more than one good hospital in the same state. So if you want to run a query to fetch a list of good hospitals based upon state. You can do it using the Row Number function.

Row Number function will display the result as Hospital name, State, and sequence as 1. If there are more hospitals in the same state then the sequence will be 1, 2, 3 … n. The moment the New state will start the sequence will be reset to 1 and so on.

The below image is a demonstration of what we explained in the above text. Since, NC has two good hospitals the sequence is 1, 2. The sequence resets 1 every time a new state appears.

Read: How to create a database in Oracle 19c

Oracle how to add row number

Row_Number is a method in the oracle database that divides the data in the given partition or order by sequence.

At first, Row_Number may appear similar to rownum but there is a difference between them. Rownum will always display the original sequence number which we cannot change.

But In Row_Number() sequence appears based upon the provided order and partition column. We can control the sequence as per our requirements.

Syntax:

Below syntax shows, the right way of using Row_number() in oracle database 19c & 21c. Order by is a mandatory option. You can provide different column names for partition and order by.

ROW_NUMBER() OVER(
        PARTITION BY COL_NAME
        ORDER BY COL_NAME [ASC|DESC]
        );

Below are the steps to add Row Number in the oracle database:

• It is mandatory to provide order by option with row_num() method.
• row number is created based upon partion by column name.

Script:

The table in the script is based upon the hospitals in the United States of America. Columns are sorted in descending order and row_number is created based upon the portion column.

SELECT 
    ID,NAME, CITY, 
    STATE, STATUS, COUNTRY,
    row_number() over(PARTITION BY STATUS order by state desc) as The_Row_number
from hospitals_in_usa;

Output:

The below output is marked in 3 colors. The blue color in the state column shows the names of the states in descending order. The Green and Pink colors show the data partitioning based upon the status.

Row_Number created a sequence for closed status and open status separately. Please notice that counting started from 1 for Open status.

Read: How to Check Oracle Database Version

Oracle select add row number

Using Select statement in oracle database row_number() method can be applied on preferred column. Row_Number offers two clauses that can be applied while creating Row_number using the Select statement in the oracle database.

These 2 clauses are:

1. Partition by
2. Order by

Partition by clause

• Partition by clause uses the divide and conquer method splits the data into groups based upon provided column name.
• The oracle partition by clause sets a range of records that will be used for each group within the over clause.
• The row_number are created based upon the provided partion column name in oracle database.
• If Partition by clause is missing then row_number is created with order by clause.

Order by clause

• The order by is a mandatory clause. It determines the arrangement of records in either asceding or descending order.
• bydefault order by clause sorts the records in ascending order (asc) by you set it to descending order by using desc keyword.

In the above section – Oracle how to add row number we have shared an example in which we have used Select.

Read: How to Get List all Tables in Oracle

Oracle sql developer add row number

Oracle SQL developer tool is software on which huge oracle queries can be executed easily. It provides both Graphical User Interface (GUI) and Command-line Interface (CLI) to execute oracle queries.

Use the below query on the worksheet of the oracle SQL developer tool. On running this query the output will be as mentioned in the above section – Oracle how to add row number.

SELECT 
    ID,NAME, CITY, 
    STATE, STATUS, COUNTRY,
    row_number() over(PARTITION BY STATUS order by state desc) as The_Row_number
from hospitals_in_usa;

Read: Number Datatype in Oracle

Oracle add row_number to select statement

We have covered row_number in the previous sections of the tutorial, here we will demonstrate another example for row_number in the oracle database.

Oracle row_number is always added using select statements in oracle database 19c & 21c. In the below script, using the Row_number portion we have divided the data based upon the states in the United State of America.

Script:

SELECT NAME, CITY, STATE, STATUS, ROW_NUMBER()
OVER(PARTITION BY STATE ORDER BY STATUS)As The_Row_Number
FROM HOSPITALS_IN_USA;

Output:

In the below output, the Row Number is column is added using a select statement.

• Using partition by clause in Row_number we have grouped data based upon the state.
• Value 1 in the row number column shows the unique entry of states.
• Blue marked values shows repetition of state as a result of which the row number values satrted following a sequence.
• The sequence is reset every time new state appears in the next row.
• This way , row number is showing the classification of data based upon the state.

You may also like to read the following Oracle tutorials.

• Database Size in Oracle
• Oracle Create Index
• Oracle Drop Database
• How to create table in Oracle
• Alter Table Add Column Oracle
• Oracle Add a Column to Table
• Oracle to_char Date Format
• Oracle get database name
• Connect to oracle database

So, in this tutorial, we have learned how to add row numbers to the oracle database. Moreover, we have discussed the following topics.

• What is Add Row Number in Oracle database
• Oracle how to add row number
• Oracle add row number column
• Oracle sql developer add row number
• Oracle add row_number to select statement