Usage of rank in sql

The RANK function in SQL ranks rows after sorting by the specified column. The result set returns the ranking of each row, starting from 1, and the same value has the same ranking. The partition expression can specify the partition column, ranking is only performed within each partition, the sort expression specifies the sort column, and the ranking is determined according to the order of the data in this column.

Usage of RANK function in SQL

RANK function is used to rank a set of rows based on specified columns the order of the data. It is an aggregate function that returns the rank of each row in the aggregated result set.

Syntax

<code>RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)</code>

Parameters

• partition_expression: Optional, specify the partition column . If specified, ranking is only done within each partition.
• order_expression: Specifies the sorting column. The rows will be ranked based on the order of the data in this column.

Return value

RANK function returns an integer representing the ranking of each row. Ranking starts at 1, with the first row having the highest rank. If multiple rows have the same value, they will have the same rank.

Example

<code class="sql">SELECT department_id, employee_name, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employee_table;</code>

Result

<code>department_id | employee_name | rank
------------- | ------------- | ----
1             | John Smith    | 1
1             | Jane Doe      | 2
2             | Peter Parker  | 1
2             | Mary Johnson | 2</code>

In this example, we press # for employee_table ##department_id Partition and sort by salary descending order. For each department, employees are ranked based on their salary.

Note

If no partition expression is specified, ranking will be performed across the entire table.
• RANK function is sensitive to duplicate values. If multiple rows have the same value, they will have the same rank.
• RANK function is similar to DENSE_RANK function, but DENSE_RANK function does not skip ranking of duplicate values.

The above is the detailed content of Usage of rank in sql. For more information, please follow other related articles on the PHP Chinese website!