Cloud

rank

The rank() window function determines the rank of a value in a group of values, based on the ORDER BY expression in the OVER clause. It can be used with all data types supported by Redpanda SQL.

Syntax

The syntax for this function is:

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

Rows with equal values for the ranking criteria receive the same rank. The output type for this function is bigint and it indicates the rank of values in a table, regardless of the input types.

  • If the optional PARTITION BY expression is present, the rankings are reset for each group of rows

  • If the ORDER BY expression is omitted then all ranks are equal to 1

Parameters

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

Examples

The following examples use the winsales table that stores details 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);

rank() with ORDER BY

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

SELECT salesid, qty,
    RANK() OVER (ORDER BY qty)
FROM winsales
ORDER BY 2,1;

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

 salesid | qty | rank
---------+-----+------
   10001 |  10 |    1
   10006 |  10 |    1
   30001 |  10 |    1
   40005 |  10 |    1
   30003 |  15 |    5
   20001 |  20 |    6
   20002 |  20 |    6
   30004 |  20 |    6
   10005 |  30 |    9
   30007 |  30 |    9
   40001 |  40 |   11
(11 rows)

rank() with ORDER BY and PARTITION BY

This example executes the 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 rank to each row:

SELECT salesid, sellerid, qty,
    RANK() OVER (PARTITION BY sellerid ORDER BY qty)
FROM winsales
ORDER BY 2,3,1;

The query returns:

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