Oracle ROW_NUMBER Function Explained

1 year ago
Liam
1 minute

The ROW_NUMBER function in Oracle assigns a unique row number to each row in the query result set, allowing for sorting and pagination of the results.

The syntax of the ROW_NUMBER function is as follows:

ROW_NUMBER() OVER (ORDER BY column1, column2, ...)

The concept of window functions is utilized, assigning a row number to each row in the result set by specifying a sorting rule in the OVER clause. The sorting rule can be defined based on one or multiple columns to determine the order of the row numbers.

Using the ROW_NUMBER function can easily achieve pagination. For instance, by using ROW_NUMBER, the query results can be sorted by a specific column and the number of rows returned can be limited, thus achieving the effect of pagination.

Below is an example of a paginated query using the ROW_NUMBER function:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY column1) AS row_num
    FROM table_name
) t
WHERE t.row_num BETWEEN 1 AND 10;

The above query will return the top 10 rows of data from the table_name table sorted by column1, with each row being assigned a row number.

bannerAds