ROWNUM

ROWNUM is a pseudo column in Oracle that assigns a unique, sequential number to rows in the result set of a query. It's used to limit the number of rows returned by a query or to order results in a specific way based on row numbers.

1. What is ROWNUM?

ROWNUM is a pseudo column provided by Oracle, which is automatically included in the result set of every query. It returns a unique, sequential number for each row, starting from 1, for each row returned by a query.

Note that ROWNUM is assigned before the ORDER BY clause is applied, so it can produce unexpected results if you are relying on it for ordering.

2. Syntax for Using ROWNUM

The general syntax for ROWNUM is:

SELECT ROWNUM, column_name

FROM table_name;

This will return the row number for each row in the table_name.

Example:

SELECT ROWNUM, employee_name

FROM employees;

This query will return each employee's name along with a unique row number.

 

3. Using ROWNUM with a WHERE Clause

ROWNUM is often used to restrict the number of rows returned by a query. For example, if you only want the first 10 rows of a query:

SELECT * 

FROM employees

WHERE ROWNUM <= 10;

This query will return the first 10 rows from the employees table.

 

4. Important Considerations with ROWNUM

·        ROWNUM is assigned before ORDER BY: Oracle assigns the row number before the ORDER BY clause is processed. This means that if you apply an ORDER BY clause after using ROWNUM, the row numbers will not reflect the final order of the result set.

For example:

SELECT ROWNUM, employee_name

FROM employees

WHERE ROWNUM <= 10

ORDER BY employee_name;

In this case, the ROWNUM is applied before the ORDER BY clause, so it may not return the first 10 employees in alphabetical order.

·        Use ROWNUM for pagination: When you want to fetch a certain range of rows, ROWNUM can be used in combination with subqueries. For example, to get rows 11 to 20:

SELECT * 

FROM (

  SELECT * 

  FROM employees

  ORDER BY employee_name

) 

WHERE ROWNUM <= 20

AND ROWNUM > 10;

 

5. Using ROWNUM with ORDER BY (Correct Approach)

To get a set of rows in a specific order and limit the result set using ROWNUM, you need to nest your query. The correct approach is to use a subquery, so the ORDER BY is applied before the ROWNUM is assigned. Here's how you can limit the results after ordering them:

SELECT * 

FROM (

  SELECT employee_name

  FROM employees

  ORDER BY employee_name

) 

WHERE ROWNUM <= 10;

In this query:

·        The inner query orders the rows.

·        The outer query then limits the number of rows returned to 10.

---

6. ROWNUM vs. ROW_NUMBER()

ROWNUM is often compared to ROW_NUMBER(), but they are not the same:

·        ROWNUM: Sequential number assigned before ORDER BY is applied. It's typically used for limiting rows or for a quick row identifier in a result set.

·        ROW_NUMBER(): A more flexible analytic function that allows for partitioning, sorting, and more control over row numbering.

For example:

SELECT employee_name, ROW_NUMBER() OVER (ORDER BY employee_name) AS row_num

FROM employees;

This gives a row number based on the order of employee_name and allows much more control when working with ordered or partitioned data.

 

7. Common Use Cases for ROWNUM

·        Limiting Results: To return only a specified number of rows:

·        SELECT * FROM employees WHERE ROWNUM <= 5;

This retrieves the first 5 rows from the employees table.

Pagination: Often used in combination with subqueries to implement pagination, such as fetching records 11–20:

SELECT * 

FROM (

  SELECT * 

  FROM employees

  ORDER BY employee_name

) 

WHERE ROWNUM > 10 AND ROWNUM <= 20;

Filtering Rows: You can filter rows based on the row number to restrict which rows are returned.

 

8. Example: Using ROWNUM to Select First N Rows

SELECT * FROM employees WHERE ROWNUM <= 5;

This query will retrieve the first 5 rows from the employees table based on how Oracle retrieves them (typically the order in which they are stored).

 

9. Limitations of ROWNUM

·        Unreliable for sorted results: If you are using ROWNUM to retrieve rows after an ORDER BY clause, the results may be unexpected because the row numbers are assigned before sorting.

·        No direct support for dynamic ranges: ROWNUM works well for fetching the first N rows, but for more dynamic or complex filtering (like rows between 10 and 20), you need a nested query or a different approach (like ROW_NUMBER()).

 

10. Using ROWNUM for Efficient Querying

·        Performance Optimization: ROWNUM can be a good choice for quickly limiting the number of rows returned, especially in large tables, as it reduces the size of the result set returned by the query, reducing overhead.

 

11. Advanced ROWNUM Usage with Subqueries

You can combine ROWNUM with complex queries. For instance, you can use ROWNUM in a subquery to get rows in a certain range after sorting:

SELECT * FROM (

  SELECT employee_name, ROWNUM AS row_num

  FROM employees

  ORDER BY employee_name

) WHERE row_num BETWEEN 10 AND 20;

In this example, the inner query orders the employees by employee_name and assigns row numbers. The outer query then fetches the rows where the row number is between 10 and 20.

 

12. ROWNUM in Oracle vs. LIMIT in Other Databases

While databases like MySQL and PostgreSQL use the LIMIT keyword to restrict the number of rows returned, Oracle uses ROWNUM or FETCH FIRST clauses in its queries. For example, in MySQL:

SELECT * FROM employees LIMIT 10;

In Oracle, the equivalent would be:

SELECT * FROM employees WHERE ROWNUM <= 10;