How to use rank function in oracle

The RANK() function in Oracle is used to sort the data group and assign the ranking: assign the same value to the same ranking Sort in the order of increasing value Starting from 1 to assign the ranking If there are duplicate values, the next value The ranking will be skipped

##RANK() function in Oracle

RANK() function is used to rank a group of The data is sorted and rankings assigned. It assigns identical values ​​to the same rank and sorts them in increasing value order.

Syntax

<code class="sql">RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)</code>

Parameters

• partition_expression (optional): used for An expression that divides data into groups. If not specified, all rows are sorted overall.
• order_expression: Expression used to sort data. Can be a column, expression, or function.

How it works

RANK() function finds the position of each value within a group and assigns the following ranking:

The same value is assigned the same ranking.
• As the value increases, the ranking also increases.
• Assign rankings starting from 1.

Example

<code class="sql">SELECT id, name, RANK() OVER (PARTITION BY job ORDER BY salary) AS job_rank
FROM employees;</code>

Result:

idnamejob_rank1John Doe12Jane Smith2##34##5Michael Scott 1

<code>
**注意**

* 如果有多个具有相同排名的值，则下一个值将跳过该排名。</code>

Peter Parker	1
Mary Jones	2

The above is the detailed content of How to use rank function in oracle. For more information, please follow other related articles on the PHP Chinese website!