Cloud

row_number

The row_number() window function returns the number of the current row within its partition (counting from 1), 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:

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

The function returns a value of type bigint. Rows with equal values for the ORDER BY expression receive different row numbers nondeterministically.

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);

row_number() with ORDER BY

This example executes the row_number() function with ORDER BY keyword, assigns a row number to each row, and orders the table by the row number (the results are sorted after the window function results are applied):

SELECT salesid, qty,
  ROW_NUMBER() OVER (ORDER BY salesid)
FROM winsales
ORDER BY 3;

The query returns:

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

row_number() with ORDER BY and PARTITION BY

This example executes the row_number() function with ORDER BY keyword and PARTITION BY clause, partitions the table by seller ID, assigns a row number to each row, and orders the table by the sales ID and row number (the results are sorted after the window function results are applied):

SELECT salesid, sellerid, qty,
  ROW_NUMBER() OVER (PARTITION BY sellerid ORDER BY salesid)
FROM winsales
ORDER BY 1;

The query returns:

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

Time series: assigning sequential row numbers by date

This example assigns a sequential row number to each sale ordered by dateid:

SELECT dateid, salesid, qty,
  ROW_NUMBER() OVER (ORDER BY dateid, salesid) AS time_series_position
FROM winsales
ORDER BY dateid, salesid;

The query returns:

   dateid   | salesid | qty | time_series_position
------------+---------+-----+----------------------
 2003-08-02 |   30001 |  10 |                    1
 2003-12-24 |   10001 |  10 |                    2
 2003-12-24 |   10005 |  30 |                    3
 2004-01-09 |   40001 |  40 |                    4
 2004-01-18 |   10006 |  10 |                    5
 2004-02-12 |   20001 |  20 |                    6
 2004-02-12 |   40005 |  10 |                    7
 2004-02-16 |   20002 |  20 |                    8
 2004-04-18 |   30003 |  15 |                    9
 2004-04-18 |   30004 |  20 |                   10
 2004-09-07 |   30007 |  30 |                   11
(11 rows)