How to use rank(over() in sql

The RANK() OVER() function in SQL is used to assign ranking values ​​to data records. It accepts an ORDER BY clause specifying the columns to rank by and the sort order. Parameters include: column name (column to be ranked), sort order (ascending or descending), and how NULL values ​​are handled (first, last, or only non-NULL values). This function is used to assign the same rank or unique rank to records with the same value, and can exclude or handle NULL values.

RANK() OVER() usage in SQL

RANK() OVER() function is used in SQL Used to rank data and assign a ranking value to each record. This function accepts an ORDER BY clause specifying the columns to rank by and the sort order.

Syntax:

<code>RANK() OVER (ORDER BY 列名 [ASC|DESC] [NULLS FIRST|LAST|ONLY])</code>

Parameters:

• Column name: Specify participation ranking of columns.
• ASC|DESC:Specify the ranking order (ascending or descending).
• NULLS FIRST|LAST|ONLY: Specify how to handle NULL values.

Usage:

RANK() OVER() function is used to rank various types of data, including:

• Assign the same ranking to records with the same value
• Assign unique rankings to records with different values
• Exclude NULL values ​​or rank NULL values ​​first/last

Example:

The following example ranks the records in the "Students" table based on the "Score" column, from high to low:

<code>SELECT *, RANK() OVER (ORDER BY 分数 DESC) AS 排名
FROM 学生;</code>

The results are as follows:

##1小明951##234

Student Number	Name	Score	Ranking
小华	90	2
小丽	85	3
Xiaogang	80	4

Note:

RANK() The ranking value returned by OVER() starts from 1, not 0.

• If there are multiple records with the same value in the column ranked by, those records will be assigned the same ranking value.
• For NULL values, the NULLS FIRST option ranks NULL values ​​first, the NULLS LAST option ranks NULL values ​​last, and the NULLS ONLY option ranks only non-NULL values.

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