Skip to main content

RANK and DENSE_RANK functions

RANK function

The RANK function is used to assign a rank to each row within a partition of a result set. The rank of a row is one plus the number of ranks that come before it. It is commonly used in scenarios where you need to rank items based on certain criteria.

Syntax

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

Arguments

  • partition_expression: Optional. Specifies the columns that define the partition.

  • sort_expression: Specifies the columns that define the order of the rows.

Returns

Returns an integer representing the rank of each row within the partition of a result set. - Rows with equal values for the ranking criteria receive the same rank. - The next rank after a tie is incremented by the number of tied rows.

Examples

Basic ranking

-- Example 1: Rank employees by salary
> SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank
  FROM employees;

-- Result with sample data:
-- | name     | salary | rank |
-- |----------|--------|------|
-- | Bob      | 90000  | 1    |
-- | Diana    | 90000  | 1    |
-- | Eve      | 85000  | 3    |
-- | Alice    | 80000  | 4    |
-- | Charlie  | 70000  | 5    |

-- Example 2: Rank employees by department and salary
> SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
  FROM employees;

-- Result:
-- | name     | department  | salary | dept_rank |
-- |----------|-------------|--------|-----------|
-- | Bob      | Engineering | 90000  | 1         |
-- | Eve      | Engineering | 85000  | 2         |
-- | Alice    | Engineering | 80000  | 3         |
-- | Diana    | Sales       | 90000  | 1         |
-- | Charlie  | Sales       | 70000  | 2         |

DENSE_RANK function

The DENSE_RANK function is similar to the RANK function, but it does not leave gaps in the ranking sequence when there are ties. It is useful when you need a continuous ranking sequence.

Syntax

DENSE_RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC|DESC], ... )

Arguments

  • partition_expression: Optional. Specifies the columns that define the partition.

  • sort_expression: Specifies the columns that define the order of the rows.

Returns

Returns an integer representing the rank of each row within the partition of a result set. - Rows with equal values for the ranking criteria receive the same rank. - The next rank after a tie is incremented by one.

Examples

Dense ranking

-- Example 1: Dense rank employees by salary
> SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
  FROM employees;

-- Result with sample data:
-- | name     | salary | dense_rank |
-- |----------|--------|------------|
-- | Bob      | 90000  | 1          |
-- | Diana    | 90000  | 1          |
-- | Eve      | 85000  | 2          |
-- | Alice    | 80000  | 3          |
-- | Charlie  | 70000  | 4          |

-- Example 2: Dense rank employees by department and salary
> SELECT name, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_dense_rank
  FROM employees;

-- Result:
-- | name     | department  | salary | dept_dense_rank |
-- |----------|-------------|--------|-----------------|
-- | Bob      | Engineering | 90000  | 1               |
-- | Eve      | Engineering | 85000  | 2               |
-- | Alice    | Engineering | 80000  | 3               |
-- | Diana    | Sales       | 90000  | 1               |
-- | Charlie  | Sales       | 70000  | 2               |