ROW_NUMBER

Overview

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 Oxla.

Syntax

The syntax for this function is as follows:

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

The output’s type for this function is BIGINT. Rows with equal values for the ORDER BY expression receive different row numbers nondeterministically.

Parameters

• (): this function takes no arguments but parentheses is required

Examples

For the needs of this section, we will create 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

In this example, we will focus on executing the ROW_NUMBER() function with ORDER BY keyword, assign a row number to each row and order the table by the row number (the results will be sorted after the window function results are applied):

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

Here is the output for the code above:

 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

In this example, we will focus on executing the ROW_NUMBER() function with ORDER BY keyword and PARTITION BY clause, partition the table by seller ID, assign a row number to each row and order the table by the sales ID and row number (the results will be 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 output of the code above will be as follows:

  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

In this example, we will assign 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;

By executing the query above, we will get the following output:

   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)