what is rownum in oracle with code examples

Introduction

Oracle is one of the most popular relational database management systems in the world. As an Oracle developer or administrator, you might find yourself needing to use the ROWNUM keyword quite often. ROWNUM is a special keyword in Oracle that is used to assign a unique number to each row returned by a query. In this article, we will take a closer look at ROWNUM and explore its syntax and usage.

What is ROWNUM in Oracle?

ROWNUM is a built-in pseudocolumn in Oracle that assigns a unique sequential number to each row that is generated in a result set. This means that whenever a query is executed, Oracle assigns a unique number to each row of the result set, starting from 1 and incrementing by 1 for each subsequent row.

ROWNUM can be particularly useful when you want to limit the number of results returned by a query. For instance, if you have a table with a large number of rows and you only want to return the first 10 rows, you can use the ROWNUM keyword to limit the output.

Syntax of ROWNUM

ROWNUM is a reserved keyword in Oracle and therefore, it does not need to be declared or initialized. Instead, it can be used within a SELECT statement as follows:

SELECT column1, column2, …, columnN
FROM table_name
WHERE conditions
AND ROWNUM <= n;

In the above syntax, n represents the maximum number of rows that are allowed in the result set. The WHERE clause of the SELECT statement is optional, but it can be used to filter the data and return only the desired rows.

Example 1: Using ROWNUM to limit the number of results

Suppose you have a table named "employees" with the following columns: "emp_id", "first_name", "last_name", "salary", and "department". To retrieve the first 10 rows of the "employees" table, you can use the following SQL statement:

SELECT emp_id, first_name, last_name, salary
FROM employees
WHERE ROWNUM <= 10;

In this example, the SELECT statement returns only the first 10 rows of the "employees" table, based on the ascending order of the row numbers assigned by Oracle.

Example 2: Using ROWNUM in subqueries

Another common usage of ROWNUM is to limit the number of rows returned by a subquery. This can be achieved by including the ROWNUM filter in the subquery, as shown in the following example:

SELECT *
FROM (SELECT emp_id, first_name, last_name, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC)
WHERE ROWNUM <= 5;

In this example, the inner subquery retrieves all the employees who belong to the IT department and sorts them in descending order of their salaries. The outer query then limits the result set to the first 5 rows, based on the ROWNUM filter.

Conclusion

ROWNUM is a powerful keyword in Oracle that can be used to limit the number of rows returned by a query or a subquery. It can also be used to assign a unique sequential number to each row in a result set. ROWNUM is easy to use and does not require any special declaration or initialization. However, it is important to note that ROWNUM is not a guaranteed sequence and may change during the lifetime of a query. Therefore, it should be used with caution and only in cases where you need to retrieve a specific number of rows from a table or a query.

here's more information on ROWNUM in Oracle as well as some related topics.

ROWNUM limitations

It's important to note that ROWNUM is assigned before any sorting or filtering occurs. This means that if you try to use ROWNUM in a query that includes an ORDER BY clause, the results may not be what you expect. In fact, the order of the rows before ROWNUM is applied may not be the same as the order of the rows after ROWNUM is applied.

Another important limitation of ROWNUM is that it cannot be used in an UPDATE or DELETE statement. This is because ROWNUM is a pseudocolumn, which means that its values are not actually stored in the table. Therefore, you cannot use ROWNUM to reference a specific row in an update or delete operation.

Alternative to ROWNUM: ROW_NUMBER()

If you need to assign row numbers that are based on the sorted and filtered results of a query, you can use the ROW_NUMBER() function instead of ROWNUM. The ROW_NUMBER() function is a window function that assigns a unique number to each row within a result set, based on the specified order.

Here's an example:

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num, emp_id, first_name, last_name, salary
FROM employees
WHERE department = 'IT';

In this example, the ROW_NUMBER() function assigns a unique number to each row of the result set, based on the descending order of the "salary" column. This means that the row with the highest salary gets assigned the number 1, the row with the second-highest salary gets assigned the number 2, and so on.

Partitioning with ROW_NUMBER()

Another useful feature of the ROW_NUMBER() function is that it supports partitioning. Partitioning allows you to group rows into separate partitions based on a specified column or set of columns. Within each partition, the ROW_NUMBER() function will assign a unique number to each row, based on the specified order.

Here's an example:

SELECT ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num, emp_id, first_name, last_name, salary, department
FROM employees;

In this example, the ROW_NUMBER() function assigns a unique number to each row of the result set, based on the descending order of the "salary" column, but partitioned by the "department" column. This means that each department will have its own set of row numbers, based on the salaries within that department.

Using ROWNUM for pagination

One common use case for ROWNUM is to implement pagination, which allows users to view a subset of the results at a time, rather than having to load all the results at once. To implement pagination with ROWNUM, you can use the following approach:

• Use the ROWNUM filter to limit the number of rows returned by the query.
• Use an offset parameter to specify how many rows to skip before returning the results.

Here's an example:

SELECT emp_id, first_name, last_name, salary
FROM employees
WHERE ROWNUM <= 10
AND ROWNUM > 5;

In this example, the query returns the rows with ROWNUM values between 6 and 10, effectively skipping the first 5 rows of the result set. The offset parameter can be calculated dynamically based on the current page and the number of rows per page.

Conclusion

ROWNUM is a powerful keyword in Oracle that allows you to assign unique row numbers to the result set of a query. It can be useful for limiting the number of rows returned by a query or for implementing pagination. However, it does have some limitations, such as its order-dependency and inability to be used in update or delete statements. If you need to assign row numbers to a sorted and filtered result set, the ROW_NUMBER() function is a better alternative.

Popular questions

Sure, here are five questions and their corresponding answers related to the topic of ROWNUM in Oracle:

1. What is ROWNUM in Oracle?
   Answer: ROWNUM is a built-in pseudocolumn in Oracle that assigns a unique sequential number to each row that is generated in a result set.

2. How is ROWNUM used in Oracle?
   Answer: ROWNUM is commonly used in Oracle to limit the number of rows returned by a query or to assign a unique number to each row in a result set.

3. Can ROWNUM be used in an UPDATE or DELETE statement in Oracle?
   Answer: No, ROWNUM cannot be used in an UPDATE or DELETE statement in Oracle because it is a pseudocolumn and its values are not actually stored in the table.

4. What is the syntax for using ROWNUM in Oracle?
   Answer: The syntax for using ROWNUM in Oracle is:
   SELECT column1, column2, …, columnN
   FROM table_name
   WHERE conditions
   AND ROWNUM <= n;

5. Can the ROW_NUMBER() function be used instead of ROWNUM in Oracle?
   Answer: Yes, the ROW_NUMBER() function can be used in Oracle to assign row numbers that are based on the sorted and filtered results of a query, as well as to implement pagination and partitioning.

Tag

querying