How to use rank() in sql

Core answer: The RANK() function in SQL is used to return the ranking of a specified row in the result set, based on the sorting of the values ​​in the row. Detailed Description: The RANK() function specifies partitioning and sorting expressions via the OVER clause. It ranks the rows in the result set based on the ordering of the specified column or expression. Identical values ​​have the same ranking, starting from 1. The RANK() function calculates the ranking independently within each partition, which means that rows with the same value in different partitions may be ranked differently.

RANK() function in SQL

RANK() function introduction

RANK() function returns the ranking of the specified row in the result set. Ranking is based on the ordering of the values ​​in the rows, and identical values ​​are ranked the same.

Syntax

RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)

Parameters

• partition_expression:Specify the column to be partitioned or an expression used to group the result set.
• order_expression: Specify the column or expression used to order partitions.

Usage

RANK() function is usually used to rank query results, for example:

• Rank based on sales Ranking Customers
• Ranking Events by Date
• Ranking Students by Grade

Example

The following example will be Departments rank employees and display each employee's ranking:

SELECT department, name, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS employee_rank
FROM employees;

Results

department	name	employee_rank
Sales	John Doe	1
Sales	Jane Smith	2
Marketing	Michael Jones	1
Marketing	Emily Carter	2
Finance	David Brown	1
Finance	Mary Miller	2

## Note

RANK() The ranking returned by the function starts from 1.
• If two or more rows have the same sort value, they will receive the same rank.
• RANK() function calculates the ranking independently within each partition, which means that rows with the same value in different partitions may be ranked differently.

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