SQL Reference

RANK() OVER ( [PARTITION BY partition_expression] ORDER BY sort_expression [ASC | DESC])

Example

SELECT
  product,
  price,
  RANK() OVER (
    ORDER BY price DESC
  ) AS PriceRank
FROM
  Products;

Output

| product | price | PriceRank |
|---------|-------|-----------|
| ProductD| 800   | 1         |
| ProductA| 600   | 2         |
| ProductC| 550   | 3         |
| ProductB| 450   | 4         |

Explanation

The example code ranks each row of the ‘Products’ table by the ‘price’ column in descending order; this is done with the RANK() function. The function arranges the prices from the highest to the lowest. The rank of each row is displayed in the ‘PriceRank’ column.

RANK() OVER ([PARTITION BY partition_expression, … ] ORDER BY sort_expression [ASC | DESC], …)

Example

WITH sales_data AS (
SELECT 'product1' product, 'store1' store, 10 sales
union all
SELECT 'product2', 'store2', 20
union all
SELECT 'product1', 'store2', 30
union all
SELECT 'product2', 'store1', 40
)
SELECT product, store, sales,
RANK() OVER (ORDER BY sales DESC) sales_rank
FROM sales_data;

Output

| product | store | sales | sales_rank |
|---------|-------|-------|------------|
| product2| store1|   40  |     1      |
| product1| store2|   30  |     2      |
| product2| store2|   20  |     3      |
| product1| store1|   10  |     4      |

Explanation

The RANK function is used to rank the rows based on the values of sales in descending order. The row with the highest sales gets a rank of 1, the next highest sales gets a rank of 2, and so on.

RANK() OVER ( [PARTITION BY value_expression , … [ n ] ] ORDER BY clause)

Example

WITH Sales_CTE (SalesPerson, Product, Sales) AS
(
  SELECT 'Joe', 'Apples', 1000 UNION ALL
  SELECT 'Joe', 'Pears', 500 UNION ALL
  SELECT 'Mike', 'Apples', 800 UNION ALL
  SELECT 'Mike', 'Pears', 1200 UNION ALL
  SELECT 'Mike', 'Bananas', 1500 UNION ALL
  SELECT 'Sue', 'Pears', 1500
)
SELECT SalesPerson, Product, Sales,
RANK() OVER (PARTITION BY SalesPerson ORDER BY Sales DESC) as SalesRank
FROM Sales_CTE;

Output

SalesPerson | Product | Sales | SalesRank
------------|---------|-------|----------
Joe         | Apples  | 1000  | 1
Joe         | Pears   | 500   | 2
Mike        | Bananas | 1500  | 1
Mike        | Pears   | 1200  | 2
Mike        | Apples  | 800   | 3
Sue         | Pears   | 1500  | 1

Explanation

The RANK function is being used to rank the sales by each person. It has been partitioned by the ‘SalesPerson’ column, meaning each seller gets a separate ranking starting from 1. The ORDER BY clause in the OVER function sorts the ‘Sales’ in descending order, meaning the product with the highest sales gets the top rank (1).

RANK() OVER ([<partition_by_clause>] ORDER BY <order_by_clause>)

Example

SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) salary_rank
FROM
    employees

Output

EMPLOYEE_NAME  DEPARTMENT    SALARY   SALARY_RANK
-------------  -----------   ------   -----------
John           Sales         5000     1
Smith          Sales         4500     2
Alice          Sales         4000     3
Bob            HR            6000     1
Charlie        HR            5500     2

Explanation

In this example, the RANK() function is used to create a ranking of employees’ salaries within each department. The ranking is ordered by salary in descending order, meaning an employee with the highest salary in their department will be ranked first. The PARTITION BY clause is used to split the data into partitions based on the department, meaning the rank resets for each department.