The RANK() function in SQL is used to assign a unique rank to each row within a partition of a result set. The ranks are based on the order defined by the ORDER BY clause. If two or more rows have the same value for the given ordering criteria, they will receive the same rank, and the next rank(s) will be skipped. Here's how you can use the RANK() function:
Syntax
RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)
- PARTITION BY: This clause divides the result set into partitions to which the RANK() function is applied. If this clause is omitted, the ranking is applied to the entire result set.
- ORDER BY: This clause specifies the order in which the rows in each partition are ranked.
Example
Suppose you have a table named Sales with the following columns: Salesperson, Region, and SalesAmount. You want to rank the sales amounts for each salesperson within their respective regions.
Sample Query:
SELECT
Salesperson,
Region,
SalesAmount,
RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS SalesRank
FROM
Sales
ORDER BY
Region, SalesRank;
Explanation:
1. SELECT: You select the columns you want to display, including the result of the RANK() function.
2. RANK(): Inside the RANK() function:
- PARTITION BY Region: This means the ranking will be applied separately for each region.
- ORDER BY SalesAmount DESC: This means that within each region, the sales amounts will be ranked from highest to lowest.
3. FROM Sales: This specifies the source table.
4. ORDER BY Region, SalesRank: This orders the final output by region and then by the rank.
Result Interpretation:
- In each region, the salesperson with the highest sales amount will get a rank of 1. If two salespersons have the same sales amount, they will receive the same rank, and the next rank will be skipped.
Note:
If you're looking to assign unique ranks without any gaps (where a tie in rank leads to subsequent ranks being consecutive), you might consider using the DENSE_RANK() function instead.
Example with DENSE_RANK():
SELECT
Salesperson,
Region,
SalesAmount,
DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS SalesRank
FROM
Sales
ORDER BY
Region, SalesRank;
In this case, if two salespersons are tied for the first rank, both will receive a rank of 1, and the next rank will be 2 (no ranks are skipped).
This is how you can use the RANK() function in SQL to assign ranks to rows based on specified criteria!