In Oracle database, the ROW_NUMBER function is a powerful tool that allows you to generate a unique sequential number for each row in a query’s result set. This function is often used to rank the results of a query or to filter out specific rows from a large dataset.
Syntax
The syntax for the ROW_NUMBER function is as follows:
ROW_NUMBER() OVER ([PARTITION BY column1, column2, ... ORDER BY columnX, columnY, ...])
The ROW_NUMBER function generates a unique sequential number for each row in the result set. The PARTITION BY clause divides the result set into partitions based on the specified columns, and the ORDER BY clause sorts each partition by the specified columns.
Example
Let’s take a look at an example to illustrate how the ROW_NUMBER function works:
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank, last_name, salary FROM employees;
In this example, the ROW_NUMBER function generates a unique sequential number for each row in the employees table. The ORDER BY clause sorts the result set in descending order by the salary column, so the row with the highest salary will have a rank of 1, the row with the second-highest salary will have a rank of 2, and so on.
The result set will include three columns: the rank, last_name, and salary columns. The rank column will contain the unique sequential number generated by the ROW_NUMBER function, the last_name column will contain the last name of the employee, and the salary column will contain the salary of the employee.
In conclusion, the ROW_NUMBER function is a powerful tool that allows you to generate a unique sequential number for each row in a query’s result set. It can be used to rank the results of a query or to filter out specific rows from a large dataset.