In Oracle SQL, the RANK() and DENSE_RANK() functions are used to assign a rank to the rows in a result set based on the values of one or more columns. The main difference between these two functions is how they handle ties (rows with the same value).
The RANK() function assigns a unique rank to each row in a result set, with the rank of a row being determined by the number of rows that have a value greater than or equal to the value of that row. If there are ties (rows with the same value), the rank of those rows will be the same, and the next rank will be skipped. For example, if there are three rows with the same value, the first one will have a rank of 1, the second one will have a rank of 2, the third one will have a rank of 3, and the next will be 4.
The DENSE_RANK() function also assigns a unique rank to each row in a result set, but it does not skip any rank values for tied rows. Instead, it assigns the same rank to all tied rows, and the next rank is increased by 1. For example, if there are three rows with the same value, the first one will have a rank of 1, the second one will have a rank of 1, the third one will have a rank of 1, and the next will be 2.
The DENSE_RANK() function is proper when you want to assign a unique rank to each row but still want to keep the ranking continuous, as it does not leave gaps in the ranking.
Both RANK() and DENSE_RANK() functions take an optional "ORDER BY" clause to determine the order in which the rows are ranked. For example, the following query uses the RANK() function to assign a rank to the employees based on their salary:
SELECT emp_name, salary, RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;
 and DENSE_RANK() functions in oracle?){kind=link}
0 Komentar