Numbering functions
GoogleSQL for BigQuery supports numbering functions.Numbering functions are a subset of window functions. To create awindow function call and learn about the syntax for window functions,seeWindow function calls.
Numbering functions assign values to each row based on their positionwithin the specified window. TheOVER clause syntax varies acrossnumbering functions.
Function list
| Name | Summary |
|---|---|
CUME_DIST | Gets the cumulative distribution (relative position (0,1]) of each row within a window. |
DENSE_RANK | Gets the dense rank (1-based, no gaps) of each row within a window. |
NTILE | Gets the quantile bucket number (1-based) of each row within a window. |
PERCENT_RANK | Gets the percentile rank (from 0 to 1) of each row within a window. |
RANK | Gets the rank (1-based) of each row within a window. |
ROW_NUMBER | Gets the sequential row number (1-based) of each row within a window. |
CUME_DIST
CUME_DIST()OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...]Description
Return the relative rank of a row defined as NP/NR. NP is defined to be thenumber of rows that either precede or are peers with the current row. NR is thenumber of rows in the partition.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Type
FLOAT64
Example
WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 2:59:01','F30-34')SELECTname,finish_time,division,CUME_DIST()OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)ASfinish_rankFROMfinishers;/*-----------------+------------------------+----------+-------------+ | name | finish_time | division | finish_rank | +-----------------+------------------------+----------+-------------+ | Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 0.25 | | Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 0.75 | | Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 0.75 | | Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 1 | | Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 0.25 | | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 0.5 | | Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 0.75 | | Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 1 | +-----------------+------------------------+----------+-------------*/DENSE_RANK
DENSE_RANK()OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...]Description
Returns the ordinal (1-based) rank of each row within the window partition.All peer rows receive the same rank value, and the subsequent rank value isincremented by one.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Type
INT64
Examples
WITHNumbersAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT2UNIONALLSELECT5UNIONALLSELECT8UNIONALLSELECT10UNIONALLSELECT10)SELECTx,DENSE_RANK()OVER(ORDERBYxASC)ASdense_rankFROMNumbers/*-------------------------+ | x | dense_rank | +-------------------------+ | 1 | 1 | | 2 | 2 | | 2 | 2 | | 5 | 3 | | 8 | 4 | | 10 | 5 | | 10 | 5 | +-------------------------*/WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 2:59:01','F30-34')SELECTname,finish_time,division,DENSE_RANK()OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)ASfinish_rankFROMfinishers;/*-----------------+------------------------+----------+-------------+ | name | finish_time | division | finish_rank | +-----------------+------------------------+----------+-------------+ | Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 | | Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 | | Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 | | Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 3 | | Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 | | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 | | Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 | | Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 | +-----------------+------------------------+----------+-------------*/NTILE
NTILE(constant_integer_expression)OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...]Description
This function divides the rows intoconstant_integer_expressionbuckets based on row ordering and returns the 1-based bucket number that isassigned to each row. The number of rows in the buckets can differ by at most 1.The remainder values (the remainder of number of rows divided by buckets) aredistributed one for each bucket, starting with bucket 1. Ifconstant_integer_expression evaluates to NULL, 0 or negative, anerror is provided.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Type
INT64
Example
WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 2:59:01','F30-34')SELECTname,finish_time,division,NTILE(3)OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)ASfinish_rankFROMfinishers;/*-----------------+------------------------+----------+-------------+ | name | finish_time | division | finish_rank | +-----------------+------------------------+----------+-------------+ | Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 | | Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 1 | | Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 | | Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 3 | | Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 | | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 1 | | Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 2 | | Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 3 | +-----------------+------------------------+----------+-------------*/PERCENT_RANK
PERCENT_RANK()OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...]Description
Return the percentile rank of a row defined as (RK-1)/(NR-1), where RK istheRANK of the row and NR is the number of rows in the partition.Returns 0 if NR=1.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Type
FLOAT64
Example
WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 2:59:01','F30-34')SELECTname,finish_time,division,PERCENT_RANK()OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)ASfinish_rankFROMfinishers;/*-----------------+------------------------+----------+---------------------+ | name | finish_time | division | finish_rank | +-----------------+------------------------+----------+---------------------+ | Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 0 | | Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 0.33333333333333331 | | Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 0.33333333333333331 | | Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 1 | | Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 0 | | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 0.33333333333333331 | | Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 0.66666666666666663 | | Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 1 | +-----------------+------------------------+----------+---------------------*/RANK
RANK()OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...]Description
Returns the ordinal (1-based) rank of each row within the ordered partition.All peer rows receive the same rank value. The next row or set of peer rowsreceives a rank value which increments by the number of peers with the previousrank value, instead ofDENSE_RANK, which always increments by 1.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Type
INT64
Examples
WITHNumbersAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT2UNIONALLSELECT5UNIONALLSELECT8UNIONALLSELECT10UNIONALLSELECT10)SELECTx,RANK()OVER(ORDERBYxASC)ASrankFROMNumbers/*-------------------------+ | x | rank | +-------------------------+ | 1 | 1 | | 2 | 2 | | 2 | 2 | | 5 | 4 | | 8 | 5 | | 10 | 6 | | 10 | 6 | +-------------------------*/WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 2:59:01','F30-34')SELECTname,finish_time,division,RANK()OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)ASfinish_rankFROMfinishers;/*-----------------+------------------------+----------+-------------+ | name | finish_time | division | finish_rank | +-----------------+------------------------+----------+-------------+ | Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 | | Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 | | Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 | | Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 4 | | Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 | | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 | | Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 | | Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 | +-----------------+------------------------+----------+-------------*/ROW_NUMBER
ROW_NUMBER()OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]]Description
Returns the sequential row ordinal (1-based) of each row for each orderedpartition. The order of row numbers within their peer group isnon-deterministic.
Doesn't require theORDER BY clause. If theORDER BY clause is unspecifiedthen the result is non-deterministic.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Type
INT64
Examples
WITHNumbersAS(SELECT1asxUNIONALLSELECT2UNIONALLSELECT2UNIONALLSELECT5UNIONALLSELECT8UNIONALLSELECT10UNIONALLSELECT10)SELECTx,ROW_NUMBER()OVER(ORDERBYx)ASrow_numFROMNumbers/*-------------------------+ | x | row_num | +-------------------------+ | 1 | 1 | | 2 | 2 | | 2 | 3 | | 5 | 4 | | 8 | 5 | | 10 | 6 | | 10 | 7 | +-------------------------*/WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 2:59:01','F30-34')SELECTname,finish_time,division,ROW_NUMBER()OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)ASfinish_rankFROMfinishers;/*-----------------+------------------------+----------+-------------+ | name | finish_time | division | finish_rank | +-----------------+------------------------+----------+-------------+ | Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 | | Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 | | Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 3 | | Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 4 | | Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 | | Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 | | Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 | | Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 | +-----------------+------------------------+----------+-------------*/Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-11-24 UTC.