SQL Reference - ROWNUM

ROWNUM

ROWNUM is a pseudocolumn in SQL that assigns a unique, sequential number to each row in the fetched result set, starting from 1. The assignment of numbers is performed after the execution of the WHERE clause and before the ORDER BY clause, if any. It is not a physical column and is used to limit the number of rows in the output, typically for performance optimization purposes.

Example

SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE ROWNUM <= 5;

Output

| ROWNUM | EMPLOYEE_ID | FIRST_NAME | LAST_NAME |
|--------|-------------|------------|-----------|
| 1      | 234         | John       | Doe       |
| 2      | 235         | Jane       | Smith     |
| 3      | 236         | Peter      | Parker    |
| 4      | 237         | Tony       | Stark     |
| 5      | 238         | Bruce      | Wayne     |

Explanation

In the SQL example given, the ROWNUM pseudocolumn is used in conjunction with the WHERE clause to limit the output to the first 5 rows retrieved from the EMPLOYEES table. The column sequence for the output is ROWNUM, EMPLOYEE_ID, FIRST_NAME, LAST_NAME.