# Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/row-number.md

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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 <a href="/sql-reference/sql-data-types/overview" target="_blank">data types</a> supported by Oxla.

## Syntax

The syntax for this function is as follows:

```sql  theme={null}
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:

```sql  theme={null}
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):

```sql  theme={null}
SELECT salesid, qty
  ROW_NUMBER() OVER (ORDER BY salesid)
FROM winsales
ORDER BY 3;
```

Here is the output for the code above:

```sql  theme={null}
 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):

```sql  theme={null}
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:

```sql  theme={null}
  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`:

```sql  theme={null}
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:

```sql  theme={null}
   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)
```