A brief discussion of row_number(), rank(), and dense_rank() in Oracle

The following article will talk to you about row_number(), rank(), and dense_rank() in Oracle. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Oracle: row_number(), rank(), dense_rank()

Syntax: ROW_NUMBER( ) OVER();

row_number has a wide range of uses. It is best to use it for sorting. It will generate a serial number for each row of records queried and sort them in order without duplication. Note When using the row_number function, you must use the over clause to select a column to sort in order to generate a sequence number. The

rank function is used to return the ranking of each row within the partition of the result set. The ranking of a row is the number of previous rankings of the relevant row plus one. Simply put, the rank function ranks the queried records. Different from the row_number function, the rank function takes into account the situation that the sorting field values ​​​​in the over clause are the same. If the rank function is used to generate the serial number, the sorting field in the over clause Serial numbers with the same value are the same. Serial numbers with different field values ​​in the following will skip the same ranking number and be ranked next. That is, the ranking number before the relevant row plus one can be understood as generating a sequence number based on the current number of records. The records are deduced and so on.

dense_rankThe function of the function is similar to the rank function. The dense_rank function is continuous when generating serial numbers, while the serial numbers generated by the rank function may be discontinuous. When the same ranking appears in the dense_rank function, the same ranking number will not be skipped, and the rank value will follow the previous rank value. Within each group, rank() is a jump sorting. When there are two first places, the third place will follow. dense_rank() is a continuous sorting. When there are two first places, the second place will still be followed.

About Partion by: The Partition by keyword is part of the analytical function in Oracle and is used to partition the result set. The difference between it and the aggregate function Group by is that it only ranks the original data and can return multiple records in a group (the number of records remains unchanged), while Group by aggregates statistics on the original data and generally only has one response Results of statistical values ​​(one per group returned).

TIPS:

When using rank over(), the null value is the largest. If the sorting field is null, it may cause the null field to be ranked first, which will affect Sort results.

It can be like this: rank over(partition by course order by score desc nulls last)

Summary:

You need to pay attention when using the ranking function The following three points:

1. The ranking function must have an OVER clause.

2. The ranking function must have an OVER clause containing ORDER BY.

3. Sort from 1 within the group.

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of A brief discussion of row_number(), rank(), and dense_rank() in Oracle. For more information, please follow other related articles on the PHP Chinese website!