The RANK() function in SQL is a window function that assigns a unique rank to each row within a partition of a result set. The rows are ordered based on a specified column or columns. When rows have the same value in the order by column(s), they receive the same rank, and the next rank(s) will be skipped.
Here’s a general syntax for using the RANK() function:
SELECT column1, column2, ...,
RANK() OVER (PARTITION BY partition_column ORDER BY order_column) AS rank
FROM table_name;
Components of the Syntax:
- SELECT clause: This is where you specify the columns you want to display in your results.
- RANK() function: This function calculates the rank of each row.
- OVER clause: This specifies how to partition and order the result set.
- PARTITION BY: This specifies the column(s) that define the partitions. The RANK() function will restart counting at 1 for each partition.
- ORDER BY: This defines the column(s) that determine the order in which the rows are ranked within each partition.
Example:
Imagine a table Sales with the following structure:
| Salesperson | Region | Sales |
|-------------|-------------|-------|
| John | West | 500 |
| Jane | East | 700 |
| Alice | West | 700 |
| Bob | East | 400 |
If you want to rank salespeople based on their sales within each region, you could write a query like this:
SELECT Salesperson,
Region,
Sales,
RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) AS SalesRank
FROM Sales;
Result:
The result of this query would look like this:
| Salesperson | Region | Sales | SalesRank |
|-------------|--------|-------|-----------|
| Jane | East | 700 | 1 |
| Bob | East | 400 | 2 |
| Alice | West | 700 | 1 |
| John | West | 500 | 2 |
In this example:
- In the East region, Jane ranks 1st with 700 sales, and Bob ranks 2nd with 400 sales.
- In the West region, both Alice and John have their respective ranks based on their sales amounts.
Key Points:
- If two or more rows tie in their order, they receive the same rank, and the next rank skips the appropriate number. For instance, if two people are rank 1, the next rank will be 3 (i.e., 1, 1, 3).
- The RANK() function is useful for generating rankings where ties should be given the same rank.
You can use DENSE_RANK() if you want consecutive rankings without gaps for ties.