> ## 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 data types 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) ```