Cloud

dense_rank

The dense_rank() window function assigns a rank for each value within a specified group, based on the ORDER BY expression in the OVER clause. Unlike the rank() function, which can leave gaps in the ranking sequence when there are ties, dense_rank() provides consecutive rank values without any gaps. This function can be used with all data types supported by Redpanda SQL.

Syntax

The syntax for this function is:

DENSE_RANK() OVER (
  [PARTITION BY partition_expression]
  ORDER BY sort_expression
)

The output type for this function is a bigint and it indicates the rank of values in a table, regardless of the input types. If the ORDER BY expression is omitted, all ranks default to 1. If an optional PARTITION BY expression is included, the rankings are reset for each group of rows. The rows with equal values for the ranking criteria receive the same rank.

Unlike rank() function, there is no gap in the sequence of ranked values (if two rows are ranked 1, the next rank is 2)

Parameters

  • (): This function does not take any arguments, but the parentheses are required.

  • PARTITION BY: Optional. Divides the result set into partitions, each processed independently. If omitted, the entire result set is treated as a single partition.

  • ORDER BY: Order of rows in each partition to which the function is applied.

Examples

The following examples use a winsales table that stores information about some sales transactions:

CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid text,
  qty int,
  qty_shipped int);
INSERT INTO winsales VALUES
    (30001, '8/2/2003', 3, 'b', 10, 10),
    (10001, '12/24/2003', 1, 'c', 10, 10),
    (10005, '12/24/2003', 1, 'a', 30, null),
    (40001, '1/9/2004', 4, 'a', 40, null),
    (10006, '1/18/2004', 1, 'c', 10, null),
    (20001, '2/12/2004', 2, 'b', 20, 20),
    (40005, '2/12/2004', 4, 'a', 10, 10),
    (20002, '2/16/2004', 2, 'c', 20, 20),
    (30003, '4/18/2004', 3, 'b', 15, null),
    (30004, '4/18/2004', 3, 'b', 20, null),
    (30007, '9/7/2004', 3, 'c', 30, null);

dense_rank() with ORDER BY

This example executes the dense_rank() function with ORDER BY keyword and calculates the descending dense rank of all rows based on the quantity sold:

SELECT salesid, qty,
  Dense_RANK() OVER (ORDER BY qty DESC) AS d_rnk,
  RANK() OVER (ORDER BY qty DESC) AS rnk
FROM winsales
ORDER BY 2,1;

Output that includes the sales ID along with the quantity sold and both dense and regular ranks:

  salesid | qty | d_rnk | rnk
---------+-----+-------+-----
   10001 |  10 |     5 |   8
   10006 |  10 |     5 |   8
   30001 |  10 |     5 |   8
   40005 |  10 |     5 |   8
   30003 |  15 |     4 |   7
   20001 |  20 |     3 |   4
   20002 |  20 |     3 |   4
   30004 |  20 |     3 |   4
   10005 |  30 |     2 |   2
   30007 |  30 |     2 |   2
   40001 |  40 |     1 |   1
(11 rows)

dense_rank() with ORDER BY and PARTITION_BY

This example executes the dense_rank() function with ORDER BY keyword and PARTITION BY clause, partitions the table by seller ID, orders each partition by the quantity, and assigns a dense rank to each row:

SELECT salesid, sellerid, qty
  DENSE_RANK() OVER (PARTITION BY sellerid ORDER BY qty DESC) AS d_rnk
FROM winsales
ORDER BY 2,3,1;

The query returns:

 salesid | sellerid | qty | d_rnk
---------+----------+-----+-------
   10001 |        1 |  10 |     2
   10006 |        1 |  10 |     2
   10005 |        1 |  30 |     1
   20001 |        2 |  20 |     1
   20002 |        2 |  20 |     1
   30001 |        3 |  10 |     4
   30003 |        3 |  15 |     3
   30004 |        3 |  20 |     2
   30007 |        3 |  30 |     1
   40005 |        4 |  10 |     2
   40001 |        4 |  40 |     1
(11 rows)