How to use rank function in sql

The RANK() function in SQL is used to rank rows in the query result set, assigning a ranking value that represents the relative position of each row in the group or table. Usage: RANK() OVER ([PARTITION BY grouping field] ORDER BY sorting field). Parameters: PARTITION BY grouping field (optional): Group the result set and rank each group separately. ORDER BY sorting field: the column to rank by. Return value: An integer representing the relative ranking of the row.

Usage of RANK() function in SQL

RANK() function is used in SQL to evaluate query results Concentrated rows are ranked. It assigns a ranking value that represents each row's relative position within the group or the entire table.

Usage:

<code class="sql">RANK() OVER ( [PARTITION BY 分组字段] ORDER BY 排序字段)</code>

Parameters:

• PARTITION BY Grouping field: Can select. Group the result set and rank each group separately.
• ORDER BY Sorting field: The column by which the ranking is based.

Return value:

An integer representing the relative ranking of the row.

Example:

Query the sales ranking of each product in the sales table:

<code class="sql">SELECT product_id, product_name,
RANK() OVER (PARTITION BY product_id ORDER BY sales_count DESC) AS sales_rank
FROM sales_table;</code>

Result:

##1Product B#22Product C13Product D1

product_id	product_name	sales_rank
1	Product A	1

Note:

If rows have the same value, they will have the same rank.
• There may be gaps in the ranking of rows, for example when there are duplicate values.
• RANK() function is similar to DENSE_RANK() function, but the latter does not skip ranking of duplicate values.

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