This page summarizes how SQL window functions are implemented across popular database dialects. Explore differences in syntax, supported features, frame clause behavior, NULL-handling rules, and vendor-specific nuances for functions such as ROW_NUMBER, RANK, LAG, LEAD, SUM OVER, and more. Use the filters to compare dialects like Postgres, Snowflake, SQL Server, Oracle, BigQuery, MySQL, and Redshift side by side.
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | Default window frame behavior is described; details include RANGE and ROWS frames and rules for window definitions. | Supports ordered-set aggregate syntax and frame clauses as documented. | SELECT SUM(salary) OVER (ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| COUNT OVER | When a window aggregate is used with ORDER BY and the default frame, it produces a running-sum type behavior; to aggregate over the whole partition omit ORDER BY or specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. | Any ordinary aggregate (such as COUNT) can be used as a window function with an OVER clause. | SELECT COUNT() OVER (PARTITION BY department) FROM employees; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| MAX OVER | When an aggregate function (like MAX) is used as a window function, it aggregates over the rows within the current row's window frame; the default frame when ORDER BY is present is from start of partition to current row (and peers). | Any built-in or user-defined ordinary aggregate (such as MAX) can be used as a window function with an OVER clause. | SELECT MAX(salary) OVER (PARTITION BY department ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| MIN OVER | When an aggregate function like MIN is used as a window function, it aggregates over the rows within the current row's window frame; the default frame when ORDER BY is present is from the start of the partition to the current row (and peers). | Any built-in or user-defined ordinary aggregate (such as MIN) can be used as a window function with an OVER clause. | SELECT MIN(salary) OVER (PARTITION BY department ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| AVG OVER | When an aggregate function like AVG is used with ORDER BY and the default frame, it computes a running aggregate from the start of the partition to the current row (including peers). | Any built-in or user-defined ordinary aggregate (such as AVG) can be used as a window function with an OVER clause. | SELECT AVG(salary) OVER (PARTITION BY department ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| LEAD | Returns the value from a row offset after the current row within the partition; if the offset row does not exist, NULL or the provided default is returned. | LEAD(value [, offset [, default ]]) is listed as a window function and requires an OVER clause. | SELECT LEAD(salary, 1) OVER (PARTITION BY dept ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| LAG | Returns the value from a row that precedes the current row within the partition; if the offset row does not exist, NULL or the provided default is returned. | LAG(value [, offset [, default ]]) is listed as a window function and requires an OVER clause. | SELECT LAG(salary, 1) OVER (PARTITION BY dept ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| NTILE | Rows are distributed into buckets as evenly as possible in the ORDER BY order; bucket numbers start at 1. | NTILE(n) assigns each row to one of n buckets as a window function and requires an OVER clause. | SELECT NTILE(4) OVER (ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| PERCENT_RANK | Returns the relative rank of the current row: (rank - 1) / (total_rows_in_partition - 1); returns 0 for the first row and 1 for the last when ordering is defined. | PERCENT_RANK() is listed as a window function and requires an OVER clause. | SELECT PERCENT_RANK() OVER (ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| CUME_DIST | Returns the cumulative distribution: the number of rows with values less than or equal to the current row, divided by the total rows in the partition. | CUME_DIST() is listed as a window function and requires an OVER clause. | SELECT CUME_DIST() OVER (ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| RANK | Equal values receive the same rank and the next rank value is skipped. | RANK() is listed as a window function and requires an OVER clause. | SELECT RANK() OVER (ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| DENSE_RANK | Equal values receive the same rank and no ranks are skipped. | DENSE_RANK() is listed as a window function and requires an OVER clause. | SELECT DENSE_RANK() OVER (ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| ROW_NUMBER | Assigns a unique sequential number to each row in the order specified; no ties share the same number. | ROW_NUMBER() is listed as a window function and requires an OVER clause. | SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| FIRST VALUE | Returns the value of the first row in the window frame; behavior depends on the frame clause (default frame may not cover the entire partition). | FIRST_VALUE(value) is listed as a window function and requires an OVER clause. | SELECT FIRST_VALUE(salary) OVER (ORDER BY salary) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| LAST VALUE | Returns the value of the last row in the window frame; with the default frame the result may reflect only rows up to the current row unless the frame is adjusted. | LAST_VALUE(value) is listed as a window function and requires an OVER clause. | SELECT LAST_VALUE(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| NTH VALUE | Returns the value of the nth row in the window frame; if the frame is not large enough to include n rows, the result is NULL. | NTH_VALUE(value , n) is listed as a window function and requires an OVER clause. | SELECT NTH_VALUE(salary, 2) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM empsalary; | yes | PostgreSQL from version 8.4 onward supports window/analytic functions (OVER, window-aggregates, ranking, navigation, distribution), making 8.4 the effective minimum version. | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | When used as a window function, SUM() produces a result for every row in the partition rather than collapsing to a single result row. | SUM(expr) can be used as a window function with an OVER clause for partitioning and ordering. | SELECT SUM(profit) OVER (PARTITION BY country) AS country_profit FROM sales; | yes | 8 | Link | Learn More |
| COUNT OVER | As a window function, COUNT() returns a value for each row in the window rather than collapsing rows; COUNT() counts all rows, COUNT(expr) counts non-NULL values. | COUNT(expr) can be used as a window function with an OVER clause that may include PARTITION BY and ORDER BY. | SELECT COUNT() OVER (PARTITION BY dept ORDER BY salary) AS dept_count FROM employees; | yes | 8 | Link | Learn More |
| MAX OVER | As a window function, MAX() returns the maximum value within the current window frame for each row rather than collapsing rows. | MAX(expr) can be used as a window function with an OVER clause including optional PARTITION BY and ORDER BY. | SELECT MAX(salary) OVER (PARTITION BY dept ORDER BY salary) AS dept_max FROM employees; | yes | 8 | Link | Learn More |
| MIN OVER | As a window function, MIN() returns the minimum value within the current window frame for each row rather than collapsing rows. | MIN(expr) can be used as a window function with an OVER clause including optional PARTITION BY and ORDER BY. | SELECT MIN(salary) OVER (PARTITION BY dept ORDER BY salary) AS dept_min FROM employees; | yes | 8 | Link | Learn More |
| AVG OVER | As a window function, AVG() returns the average value computed over the current window frame for each row rather than collapsing rows. | AVG(expr) can be used as a window function with an OVER clause including optional PARTITION BY and ORDER BY. | SELECT AVG(salary) OVER (PARTITION BY dept ORDER BY salary) AS dept_avg FROM employees; | yes | 8 | Link | Learn More |
| LEAD | Returns the value from a following row in the window; if the offset row does not exist, returns the default or NULL. | LEAD(expr [, offset [, default ]]) OVER ([PARTITION BY partition_expression] [ORDER BY order_expression]) | SELECT LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS next_salary FROM employees; | yes | 8 | Link | Learn More |
| LAG | Returns the value from a preceding row in the window; if the offset row does not exist, returns the default or NULL. | LAG(expr [, offset [, default ]]) OVER ([PARTITION BY partition_expression] [ORDER BY order_expression]) | SELECT LAG(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS prev_salary FROM employees; | yes | 8 | Link | Learn More |
| NTILE | Distributes rows into buckets as evenly as possible; bucket numbers begin at 1. | NTILE(n) must be used with an OVER clause and divides rows into n buckets; ORDER BY may be specified. | SELECT NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees; | yes | 8 | Link | Learn More |
| PERCENT_RANK | Returns (rank - 1) / (rows_in_partition − 1); the first row has 0 and the last has 1. | PERCENT_RANK() must be used with an OVER clause and computes the relative rank of a row within its partition. | SELECT PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank FROM employees; | yes | 8 | Link | Learn More |
| CUME_DIST | Returns the fraction of rows with values less than or equal to the current row; result is between 0 and 1. | CUME_DIST() must be used with an OVER clause and computes the cumulative distribution of a row within its partition. | SELECT CUME_DIST() OVER (ORDER BY salary) AS cume_dist FROM employees; | yes | 8 | Link | Learn More |
| RANK | Rows with equal sort values receive the same rank, and gaps appear in the ranking sequence when ties occur. | RANK() must be used with an OVER clause and supports optional PARTITION BY and ORDER BY. | SELECT RANK() OVER (ORDER BY salary) AS rnk FROM employees; | yes | 8 | Link | Learn More |
| DENSE_RANK | Rows with equal sort values receive the same rank, and no gaps appear in the ranking sequence. | DENSE_RANK() must be used with an OVER clause and supports optional PARTITION BY and ORDER BY. | SELECT DENSE_RANK() OVER (ORDER BY salary) AS dense_rnk FROM employees; | yes | 8 | Link | Learn More |
| ROW_NUMBER | Assigns a unique sequential integer to each row within the window; no ties share the same number. | ROW_NUMBER() must be used with an OVER clause and supports optional PARTITION BY and ORDER BY. | SELECT ROW_NUMBER() OVER (ORDER BY salary) AS row_num FROM employees; | yes | 8 | Link | Learn More |
| FIRST VALUE | Returns the first value in the window frame; default frame may limit the rows considered unless an explicit frame is provided. | FIRST_VALUE(expr) must be used with an OVER clause and supports optional PARTITION BY and ORDER BY. | SELECT FIRST_VALUE(salary) OVER (ORDER BY salary) AS first_sal FROM employees; | yes | 8 | Link | Learn More |
| LAST VALUE | Returns the last value in the window frame; with the default frame, the result may reflect only rows up to the current row unless an explicit frame is defined. | LAST_VALUE(expr) must be used with an OVER clause and supports optional PARTITION BY, ORDER BY, and explicit frame clauses. | SELECT LAST_VALUE(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sal FROM employees; | yes | 8 | Link | Learn More |
| NTH VALUE | Returns the value of the nth row in the window frame; if the frame contains fewer than n rows, the result is NULL. | NTH_VALUE(expr , n) must be used with an OVER clause and supports optional PARTITION BY, ORDER BY, and explicit frame clauses. | SELECT NTH_VALUE(salary, 2) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS nth_sal FROM employees; | yes | 8 | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | When used as a window function, SUM() cannot use the DISTINCT specifier. Warning: unsupported distinct option when used as window function. |
Aggregate functions such as SUM must include an OVER clause to work as window functions. | SELECT category, amount, SUM(amount) OVER (PARTITION BY category) AS category_sum FROM sales; | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| COUNT OVER | Aggregate functions can be used as window functions; `COUNT()` is listed among them. Warning: Aggregate functions with the DISTINCT specifier are not supported as window functions. |
Standard aggregate function used with an `OVER` clause (e.g., `COUNT() OVER ( ORDER BY ... )`). | SELECT COUNT() OVER (ORDER BY column) FROM table; | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| MAX OVER | Aggregate functions such as MAX() can be used as window functions (i.e., with OVER). Warning: Aggregate functions with the DISTINCT specifier are not supported as window functions. |
Standard aggregate function MAX() used with an OVER clause. | SELECT test, score, MAX(score) OVER (PARTITION BY test) AS max_score FROM student; | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| MIN OVER | Aggregate functions such as MIN() can be used as window functions (i.e., with OVER). Warning: Aggregate functions with the DISTINCT specifier are not supported as window functions. |
Standard aggregate function MIN() used with an OVER clause. | SELECT MIN(score) OVER (PARTITION BY name) AS lowest_score FROM student_test; | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| AVG OVER | Aggregate functions such as AVG() can be used as window functions Warning: Aggregate functions with the DISTINCT specifier are not supported as window functions |
Standard aggregate function AVG() used with an OVER clause | SELECT name, test, score, AVG(score) OVER (PARTITION BY test) AS average_by_test FROM student | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| LEAD | Returns the value from a row at a specified offset after the current row; default offset is 1 | LEAD(expr[, offset]) OVER (PARTITION BY ... ORDER BY ...) | SELECT department, employee, LEAD(salary) OVER (PARTITION BY department ORDER BY salary) AS next_salary FROM employees; | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| LAG | Accesses data from a previous row determined by the ORDER BY clause; default offset = 1 | LAG(expr[ , offset]) OVER ( [ PARTITION BY partition_expression ] ORDER BY order_list ) | SELECT department, employee, LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS previous_salary FROM employees; | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| NTILE | Divides ordered rows into n buckets and assigns a bucket number to each row | NTILE(n) OVER (PARTITION BY ... ORDER BY ...) | SELECT NTILE(4) OVER (ORDER BY score) AS quartile FROM student_scores | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| PERCENT_RANK | Calculates the relative rank of a row within its window partition as a value between 0 and 1 | PERCENT_RANK() OVER (PARTITION BY ... ORDER BY ...) | SELECT PERCENT_RANK() OVER (ORDER BY score) AS pr FROM student_scores | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| CUME_DIST | Returns the cumulative distribution of the current row within its partition (number of rows <= current row / total rows) | CUME_DIST() OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] ) | SELECT CUME_DIST() OVER (ORDER BY a) AS cum_dist FROM t1 | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| RANK | Assigns the same rank to tied rows and leaves gaps in rank values | RANK() OVER (PARTITION BY ... ORDER BY ...) | SELECT RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS rank | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| DENSE_RANK | Assigns the same rank to tied rows without leaving gaps in rank values | DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...) | SELECT ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark DESC) AS row_num | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| ROW_NUMBER | Assigns a unique sequential number to each row within the window partition based on ORDER BY | ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) | SELECT department, employee, ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS row_num FROM employees; | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| FIRST VALUE | Returns the first value in the window frame defined by the ORDER BY and frame clause | FIRST_VALUE(expr) OVER (PARTITION BY ... ORDER BY ...) | SELECT FIRST_VALUE(val) OVER (ORDER BY val) AS first_val FROM t1 | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| LAST VALUE | Returns the last value in the window frame defined by the ORDER BY and frame clause | LAST_VALUE(expr) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ... ) | SELECT LAST_VALUE(val) OVER (ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val FROM t1 | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| NTH VALUE | Returns the value from the nth row of the window frame defined by the ORDER BY and frame clause | NTH_VALUE(expr, n) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ... ) | SELECT NTH_VALUE(val, 2) OVER (ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_val FROM t1 | yes | 10.2 / 10.3 (use 10.3 for full coverage) | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | When used with OVER, SUM performs a calculation across a window of rows; behaves as an analytic function. | Supports SUM() as an analytic function using the OVER clause with PARTITION BY and ORDER BY. | SELECT SUM(SalesAmount) OVER (PARTITION BY Region ORDER BY OrderDate) AS RunningTotal FROM Sales; | yes | SQL Server 2005 | Link | Learn More |
| COUNT OVER | When used with OVER, COUNT returns the count of rows in the window; may include or exclude NULL depending on COUNT vs COUNT(column). | COUNT supports the OVER clause, with optional PARTITION BY and ORDER BY. | SELECT COUNT() OVER (PARTITION BY Department ORDER BY HireDate) AS RunningCount FROM Employees; | yes | SQL Server 2005 | Link | Learn More |
| MAX OVER | When used with OVER, MAX returns the maximum value over the specified window of rows. | MAX supports the OVER clause including PARTITION BY and ORDER BY. | SELECT MAX(Salary) OVER (PARTITION BY Department ORDER BY HireDate) AS MaxSalarySoFar FROM Employees; | yes | SQL Server 2005 | Link | Learn More |
| MIN OVER | When used with OVER, MIN returns the minimum value over the specified window of rows and ignores NULL values; for character data, finds lowest value in the collation sequence. | MIN supports the OVER clause, with optional PARTITION BY and ORDER BY. | SELECT department, salary, MIN(salary) OVER (PARTITION BY department) AS min_salary_in_dept FROM employees; | yes | SQL Server 2005 | Link | Learn More |
| AVG OVER | AVG divides the sum of non-null values by the count of non-null values; when used with OVER and ORDER BY it becomes nondeterministic. | AVG supports [ ALL | DISTINCT ] and the OVER clause with optional PARTITION BY and ORDER BY. | SELECT AVG(SalesAmount) OVER (PARTITION BY Region ORDER BY OrderDate) AS AvgSalesByRegionOverTime FROM Sales; | yes | SQL Server 2005 | Link | Learn More |
| LEAD | LEAD accesses a row at a specified physical offset that follows the current row; default is NULL if offset beyond partition; IGNORE NULLS / RESPECT NULLS supported from SQL Server 2022. | LEAD(scalar_expression [, offset [, default ]]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY ... ] ORDER BY ... ) | SELECT department, salary, LEAD(salary) OVER (PARTITION BY department ORDER BY salary) AS next_salary FROM employees; | yes | SQL Server 2012 (11.x) | Link | Learn More |
| LAG | LAG accesses a row at a specified physical offset that comes before the current row in the same result set; if offset is beyond partition returns default (or NULL); IGNORE NULLS / RESPECT NULLS supported from SQL Server 2022 (16.x). | LAG(scalar_expression [ , offset ] [ , default ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ partition_by_clause ] order_by_clause ) | SELECT LAG(SalesQuota,1,0) OVER (ORDER BY QuotaDate) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory; | yes | SQL Server 2012 (11.x) | Link | Learn More |
| NTILE | NTILE distributes rows into a specified number of groups; each group gets an integer bucket starting at 1; groups differ in size by at most one row. | NTILE(integer_expression) OVER ( [ PARTITION BY ... ] ORDER BY ... ) | SELECT NTILE(4) OVER (ORDER BY SalesYTD) AS Quartile, BusinessEntityID, SalesYTD FROM Sales.SalesPerson; | yes | SQL Server 2005 | Link | Learn More |
| PERCENT_RANK | Returns a float between 0 and 1 representing the relative rank of a row within its partition; first row = 0; NULLs are treated as lowest values and included. | PERCENT_RANK() OVER ( [ PARTITION BY ... ] ORDER BY ... ) | SELECT department, salary, PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) AS pct_rank FROM employees; | yes | SQL Server 2012 (11.x) | Link | Learn More |
| CUME_DIST | Returns the cumulative distribution of a value within its partition; NULLs are included as the lowest values; result is a float >0 and ≤1. | CUME_DIST() OVER ( [ PARTITION BY ... ] ORDER BY ... ) | SELECT department, salary, CUME_DIST() OVER (PARTITION BY department ORDER BY salary) AS cume_dist FROM employees; | yes | SQL Server 2012 (11.x) | Link | Learn More |
| RANK | Assigns the same rank to tied rows, leaving gaps in subsequent rankings; requires ORDER BY; NULLs sort lowest. | RANK() OVER ( [ PARTITION BY ... ] ORDER BY ... ) | SELECT department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM employees; | yes | SQL Server 2005 | Link | Learn More |
| DENSE_RANK | Returns the rank of each row within its partition with no gaps in ranking values; ties receive the same rank; nondeterministic. | DENSE_RANK() OVER ( [ PARTITION BY ... ] ORDER BY ... ) | SELECT department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_dense_rank FROM employees; | yes | SQL Server 2005 | Link | Learn More |
| ROW_NUMBER | Assigns a unique sequential integer to rows within a partition, ordered by the ORDER BY clause; no ties because numbering increments row-by-row. | ROW_NUMBER() OVER ( [ PARTITION BY ... ] ORDER BY ... ) | SELECT ROW_NUMBER() OVER (ORDER BY SalesYTD DESC) AS RowNum, BusinessEntityID, SalesYTD FROM Sales.SalesPerson ORDER BY SalesYTD DESC; | yes | SQL Server 2005 | Link | Learn More |
| FIRST VALUE | Returns the first value in the window frame; frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if not specified; behavior depends on frame and ORDER BY. | FIRST_VALUE(expression) OVER ( [ PARTITION BY ... ] ORDER BY ... [ ROWS/RANGE frame_clause ] ) | SELECT department, salary, FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS first_salary FROM employees; | yes | SQL Server 2012 (11.x) | Link | Learn More |
| LAST VALUE | Returns the last value in the window frame; default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which often produces non-intuitive results unless an explicit frame is specified. | LAST_VALUE(expression) OVER ( [ PARTITION BY ... ] ORDER BY ... [ ROWS/RANGE frame_clause ] ) | SELECT department, salary, LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary FROM employees; | yes | SQL Server 2012 (11.x) | Link | Learn More |
| NTH VALUE | N/a Warning: N/a |
N/a | N/a | No | N/a | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | Defaults and behavior: SUM OVER uses analytic functions framework; behavior depends on analytic_clause. No additional SUM-specific behavior noted. | Syntax: SUM(expr) OVER (analytic_clause). Supports analytic options as shown in documentation. | SELECT SUM(salary) OVER (PARTITION BY department_id) AS dept_total FROM employees; | yes | 8i | Link | Learn More |
| COUNT OVER | COUNT never returns null. DISTINCT analytic use restricts analytic_clause to PARTITION BY only. | Syntax: COUNT(expr) OVER (analytic_clause). COUNT(DISTINCT expr) analytic allows only PARTITION BY; ORDER BY and window frames not allowed. | SELECT last_name, salary, COUNT() OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS mov_count FROM employees; | yes | 8i | Link | Learn More |
| MAX OVER | Behavior: MAX returns the maximum value of expr; for analytic use it returns one row per input row within the partition/window defined by analytic_clause. (Documentation gives both aggregate and analytic examples.) | Syntax : MAX(expr) OVER (analytic_clause) - can be used as an aggregate or analytic function. | SELECT manager_id, last_name, salary, MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max FROM employees ORDER BY manager_id, last_name, salary; | yes | 8i | Link | Learn More |
| MIN OVER | Behavior: MIN returns the minimum value of expr; as an analytic function it returns a value per row over the defined window or partition. | Syntax : MIN(expr) OVER (analytic_clause). | SELECT manager_id, last_name, hire_date, salary, MIN(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE UNBOUNDED PRECEDING) AS p_cmin FROM employees ORDER BY manager_id, last_name, hire_date, salary; | yes | 8i | Link | Learn More |
| AVG OVER | Behavior: AVG returns the average of expr; for analytic use it respects the analytic_clause (partitioning, ordering, window frame) and for vector inputs NULLs are ignored and if all inputs NULL then result is NULL. | Syntax: AVG(expr) OVER (analytic_clause). For DISTINCT: only the query_partition_clause is allowed, ORDER BY and windowing_clause are not allowed. | SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees ORDER BY manager_id, hire_date, salary; | yes | 8i | Link | Learn More |
| LEAD | Behavior: LEAD returns the value of a subsequent row; if offset exceeds partition boundary, default is returned (or NULL if omitted). Evaluated after windowing; ORDER BY defines row sequence. | Syntax: LEAD(value_expr [, offset [, default ]]) OVER (analytic_clause). Offset and default are optional. | SELECT last_name, hire_date, salary, LEAD(salary,1) OVER (ORDER BY hire_date) AS next_sal FROM employees; | yes | 8i | Link | Learn More |
| LAG | Behavior: LAG returns value from a preceding row; if offset goes beyond the partition, default is returned (or NULL if omitted). Evaluated after windowing. ORDER BY defines row sequencing. | Syntax: LAG(value_expr [, offset [, default ]]) OVER (analytic_clause). Offset and default are optional. | SELECT last_name, hire_date, salary, LAG(salary,1) OVER (ORDER BY hire_date) AS prev_sal FROM employees; | yes | 8i | Link | Learn More |
| NTILE | Behavior: Divides ordered rows into expr buckets; earlier buckets may contain one more row if rows do not divide evenly. | Syntax: NTILE(expr) OVER (analytic_clause). expr determines number of buckets. | SELECT department_id, last_name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees; | yes | 8i | Link | Learn More |
| PERCENT_RANK | Behavior: Computes the percent rank of a row within its partition: (rank - 1) / (rows_in_partition - 1). Returns 0 for the first row in the ordered partition. | Syntax: PERCENT_RANK() OVER (analytic_clause). No arguments allowed. | SELECT last_name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS pr FROM employees; | yes | 8i | Link | Learn More |
| CUME_DIST | Behavior: Computes cumulative distribution for a row: number of rows with value <= current / total rows in partition. Returns a value > 0 and <= 1. Depends on ORDER BY. | Syntax: CUME_DIST() OVER (analytic_clause). No arguments are allowed. | SELECT last_name, salary, CUME_DIST() OVER (ORDER BY salary) AS cume_dist FROM employees; | yes | 8i | Link | Learn More |
| RANK | Behavior: Assigns the same rank to tied rows; rank increments by the number of tied rows (gaps appear). Depends on ORDER BY within analytic_clause. | Syntax: RANK() OVER (analytic_clause). Empty parentheses required. | SELECT department_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; | yes | 8i | Link | Learn More |
| DENSE_RANK | Behavior: Assigns consecutive ranks with no gaps; tied rows receive the same rank, and the next distinct row gets rank+1. | Syntax: DENSE_RANK() OVER (analytic_clause). Empty parentheses required. | SELECT department_id, last_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank FROM employees; | yes | 8i | Link | Learn More |
| ROW_NUMBER | Behavior: Assigns a unique, increasing integer to each row in the ordered partition; no ties share a value. | Syntax: ROW_NUMBER() OVER (analytic_clause). Empty parentheses required. | SELECT department_id, last_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employees; | yes | 8i | Link | Learn More |
| FIRST VALUE | Behavior: Returns the first value in the ordered set defined by the analytic_clause. Result depends on ORDER BY and window frame; default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if ORDER BY is present. | Syntax: FIRST_VALUE(expr) OVER (analytic_clause). expr is required; analytic_clause controls partitioning, ordering, and windowing. | SELECT department_id, hire_date, salary, FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_sal FROM employees; | yes | 8i | Link | Learn More |
| LAST VALUE | Behavior: Returns the last value in the ordered window. Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY exists, which may cause LAST_VALUE to return the current row unless a wider frame is specified. | Syntax: LAST_VALUE(expr) OVER (analytic_clause). expr is required; analytic_clause defines partition, ordering, and window. | SELECT department_id, hire_date, salary, LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sal FROM employees; | yes | 8i | Link | Learn More |
| NTH VALUE | Behavior: Returns the value of expr from the nth row in the window. Direction defaults to FROM FIRST. When ORDER BY is present, default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which may cause unexpected results unless ROWS-based frames are specified. | Syntax: NTH_VALUE(expr, n [, from_clause]) OVER (analytic_clause). n must be a positive integer; FROM FIRST or FROM LAST optionally specifies direction. | SELECT department_id, hire_date, salary, NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_sal FROM employees; | yes | 8i | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | When used as a window function with an ORDER BY clause, the implied default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Additionally: the DISTINCT keyword is prohibited in the window-function form. NULLs are ignored (if all are NULL then result is NULL). Warning: Using DISTINCT inside the window-function form is prohibited. |
SUM([ DISTINCT ] <expr1>) OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ] ) | SELECT region, amount, SUM(amount) OVER (PARTITION BY region ORDER BY amount) AS sum_over_region FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| COUNT OVER | When used as a window function, DISTINCT is prohibited in the OVER form; default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; explicit RANGE frames require a single input argument and COUNT(table.) is not supported. Warning: Using DISTINCT inside the window-function form is prohibited. |
COUNT( [ DISTINCT ] <expr1> [ , <expr2> ... ] ) OVER ( [ PARTITION BY <expr3> ] [ ORDER BY <expr4> [ ASC | DESC ] [ <window_frame> ] ] ) | SELECT region, amount, COUNT(amount) OVER (PARTITION BY region ORDER BY amount) AS count_over_region FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| MAX OVER | When used as a window function: NULL values are ignored unless all rows are NULL; the default window frame (when ORDER BY is present and no frame is specified) is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Warning: DISTINCT has no effect in aggregate form; no additional warnings are listed. |
MAX( <expr> ) [ OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ <window_frame> ] ] ) ] | SELECT region, amount, MAX(amount) OVER (PARTITION_BY region ORDER BY amount) AS max_over_region FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| MIN OVER | When used as a window function: NULL values are ignored unless all rows are NULL; if an ORDER BY clause is present and no window frame is specified, the implied frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Warning: DISTINCT has no effect in aggregate form; no additional explicit warnings listed. |
MIN( <expr> ) [ OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ <window_frame> ] ] ) ] | SELECT region, amount, MIN(amount) OVER (PARTITION BY region ORDER BY amount) AS min_over_region FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| AVG OVER | When used as a window function with ORDER BY: if no window frame is specified, the implied frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; VARCHAR inputs are implicitly cast to float and will error if cast fails. | AVG( [ DISTINCT ] <expr1> ) OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ] ) | SELECT region, amount, AVG(amount) OVER (PARTITION BY region ORDER BY amount) AS avg_over_region FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| LEAD | Accesses data in a subsequent row in the same result set without needing a self-join; supports an offset (default 1), a default return if offset is out of bounds, and {IGNORE | RESPECT} NULLS semantics. Default offset is 1; default default value is NULL. The PARTITION BY clause partitions the result and ORDER BY defines ordering in each partition. Warning: When IGNORE NULLS is specified, a maximum offset of 1,000,000 is enforced. |
LEAD ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] ) | SELECT emp_id, year, revenue, LEAD(revenue) OVER (PARTITION BY emp_id ORDER BY year) AS next_revenue FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| LAG | Accesses data in a previous row in the same partition; default offset is 1; default value is NULL; supports IGNORE NULLS and RESPECT NULLS. | LAG ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] ) | SELECT emp_id, year, revenue, LAG(revenue, 1, 0) OVER (PARTITION BY emp_id ORDER BY year) AS diff_to_prev FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| NTILE | Divides an ordered data set equally into the number of buckets specified by constant_value; buckets are numbered 1 through constant_value. | NTILE( <constant_value> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] ) | SELECT exchange, symbol, NTILE(4) OVER (PARTITION BY exchange ORDER BY shares) AS ntile_4 FROM trades ORDER BY exchange, ntile_4; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| PERCENT_RANK | Returns the relative rank of a value within a group of values, expressed from 0.0 to 1.0; supports only fixed-range window frames (e.g., RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). | PERCENT_RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <fixedRangeFrame> ] ) | SELECT exchange, symbol, PERCENT_RANK() OVER (PARTITION BY exchange ORDER BY price) AS percent_rank FROM trades; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| CUME_DIST | Returns the cumulative distribution of a value within the partition; explicit window frames are not supported. | CUME_DIST() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ ASC | DESC ] ) | SELECT symbol, exchange, CUME_DIST() OVER ( PARTITION BY exchange ORDER BY price ) AS cume_dist FROM trades; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| RANK | Returns the rank of a value within an ordered group of values; the rank starts at 1 and gaps occur when there are ties (e.g., if two rows tie for rank 1, the next rank is 3). | RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] ) | SELECT state, bushels, RANK() OVER (ORDER BY bushels DESC) as rank FROM corn_production; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| DENSE_RANK | Returns the rank of a value within a group of values, without gaps in the rank sequence (ties share the same rank and the next rank increments by 1). | DENSE_RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ ASC | DESC ] [ <window_frame> ] ) | SELECT state, bushels, DENSE_RANK() OVER (ORDER BY bushels DESC) AS dense_rank FROM corn_production; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| ROW_NUMBER | Returns a unique row number for each row within a window partition; numbering starts at 1 and increments by 1 sequentially. | ROW_NUMBER() OVER ( [ PARTITION BY <expr1> [, <expr2> ... ] ] ORDER BY <expr3> [ , <expr4> ... ] [ { ASC | DESC } ] ) | SELECT symbol, exchange, shares, ROW_NUMBER() OVER (PARTITION BY exchange ORDER BY shares) AS row_number FROM trades; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| FIRST VALUE | Returns the first value in the window frame for each row; when the current row is the very first row in the window frame, the result may be NULL. Non-existent rows outside the partition or frame are not included. | FIRST_VALUE(<expr>) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] ) | SELECT region, amount, FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount) AS first_amount FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| LAST VALUE | Returns the last value within the ordered window; default window frame for ranking functions is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING if no frame specified. Supports IGNORE NULLS and RESPECT NULLS semantics. | LAST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ <window_frame> ] ) | SELECT region, amount, LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| NTH VALUE | Returns the nth value (up to 1000) within an ordered group of values; default window frame for ranking functions is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Input n cannot exceed 1000 and the ORDER BY clause is required. | NTH_VALUE( <expr> , <n> ) [ FROM { FIRST | LAST } ] [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] ) | SELECT region, amount, NTH_VALUE(amount, 2) OVER (PARTITION BY region ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_amount FROM sales; | yes | Snowflake runs on a continuously deployed, versionless engine, so minimum version information does not apply. | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | When an aggregate function is used with the OVER clause it becomes a window function. If ORDER BY is present but no frame clause is given, the frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Warning: In a window-function call, aggregate-function modifiers such as DISTINCT or ORDER BY inside the function cannot be used together with the OVER clause. |
SUM([ DISTINCT ] expression) [ OVER over_clause ] | SELECT region, amount, SUM(amount) OVER (PARTITION BY region) AS region_sum FROM sales_table; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| COUNT OVER | When an aggregate function is used with OVER, it becomes a window function. Aggregate-function clauses such as DISTINCT cannot be used together with OVER. Warning: Aggregate-function modifiers (such as DISTINCT) cannot be used when the aggregate is used as a window function. |
COUNT([ DISTINCT ] expression) [ OVER (window_clause) ] | SELECT region, amount, COUNT(*) OVER (PARTITION BY region) AS region_count FROM sales_table; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| MAX OVER | When an aggregate function is used with OVER it becomes a window function. Aggregate-function modifiers (such as DISTINCT) cannot be used together with OVER. Warning: Aggregate-function modifiers (such as DISTINCT) cannot be used when the aggregate is used as a window function. |
MAX(expression) [ OVER (window_clause) ] | SELECT category, price, MAX(price) OVER (PARTITION BY category) AS category_max_price FROM products; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| MIN OVER | Returns the minimum non-NULL value in a group; when used with OVER it becomes a window function. If the group is empty or all values NULL, returns NULL. If any argument is NaN, returns NaN. Warning: Aggregate-function modifiers (such as DISTINCT) cannot be used when the aggregate is used as a window function. |
MIN(expression) [ OVER (window_clause) ] | SELECT category, price, MIN(price) OVER (PARTITION BY category) AS category_min_price FROM products; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| AVG OVER | Returns the average of non-NULL values in a group; when used with OVER it becomes a window function. If group is empty or all values NULL, returns NULL; if any argument is NaN, returns NaN; if floating-point result type, result may be non-deterministic. Warning: Aggregate-function clauses (e.g., DISTINCT) cannot be used when the aggregate is used as a window function. |
AVG([ DISTINCT ] expression) [ OVER (window_clause) ] | SELECT team, score, AVG(score) OVER (PARTITION BY team) AS team_avg_score FROM games_table; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| LEAD | Returns the value of the expression for a subsequent row in the window frame; the return type matches the value_expression type. ([docs] section) | LEAD(value_expression [, offset [, default_expression ]]) OVER (window_clause) | SELECT LEAD(sales) OVER (PARTITION BY region ORDER BY date) AS next_region_sales FROM sales_table; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| LAG | Returns the value of value_expression on a preceding row. - offset must be non-negative; default_expression is used when no row exists at the specified offset. (type and behavior details as documented) Warning: An error occurs if the offset is NULL or a negative value. |
LAG(value_expression[, offset [, default_expression ]]) OVER (window_clause) | SELECT division, runner_name, finish_time, LAG(runner_name) OVER (PARTITION BY division ORDER BY finish_time) AS previous_runner FROM race_results; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| NTILE | Assigns each row to one of bucket_count quantile buckets (1-based) within the window. The function returns an INT64 value. ([docs] page) | NTILE(bucket_count) OVER (window_clause) | SELECT region, amount, NTILE(4) OVER (PARTITION BY region ORDER BY amount DESC) AS quartile FROM sales; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| PERCENT_RANK | Computes the percentile rank (from 0 to 1) of each row within the window. Return type is FLOAT64. (As documented) | PERCENT_RANK() OVER ([PARTITION BY partition_expression ,...] ORDER BY sort_expression [{ASC|DESC}],...) | SELECT PERCENT_RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS pct_rank_sales FROM sales_table; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| CUME_DIST | Returns the relative rank of a row defined as NP/NR, where NP = number of rows that precede or are peers with the current row, and NR = total rows in the partition. | CUME_DIST() OVER (window_clause) | SELECT division, runner_name, finish_time, CUME_DIST() OVER (PARTITION BY division ORDER BY finish_time) AS finish_rank FROM race_results; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| RANK | Assigns the same rank to peer rows; ranks may not be consecutive. Return type is INT64. | RANK() OVER (window_clause) | SELECT RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank FROM sales_table; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| DENSE_RANK | 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 is incremented by one. | DENSE_RANK() OVER (window_clause) | SELECT department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_dense_rank FROM employees; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| ROW_NUMBER | Assigns a unique sequential integer (starting at 1) to each row within the window partition. Return type is INT64. | ROW_NUMBER() OVER (window_clause) | SELECT ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC) AS rn FROM sales_table; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| FIRST VALUE | Returns the value of value_expression for the first row in the current window frame. Includes NULLs unless IGNORE NULLS specified. | FIRST_VALUE(value_expression [{RESPECT | IGNORE} NULLS]) OVER (over_clause) | SELECT division, runner_name, finish_time, FIRST_VALUE(finish_time) OVER (PARTITION BY division ORDER BY finish_time) AS fastest_time FROM race_results; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| LAST VALUE | Returns the value of the value_expression for the last row in the current window frame. Return type is the same as value_expression. | LAST_VALUE(value_expression [{RESPECT | IGNORE} NULLS]) OVER (window_clause) | SELECT LAST_VALUE(score) OVER (PARTITION BY team ORDER BY game_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_team_score FROM team_scores; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| NTH VALUE | Returns the value of value_expression at the Nth row of the current window frame, where N is a constant integer expression. Returns NULL if no such row. NULLs are included unless IGNORE NULLS is specified. | NTH_VALUE(value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS]) OVER (window_clause) | SELECT NTH_VALUE(sales_amount, 3) OVER (PARTITION BY region ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_sale_in_region FROM sales_table; | yes | BigQuery is continuously updated and has no versioned SQL engine, so minimum version information does not apply. | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | Default frame behavior is not explicitly described; NULL-handling or ordering behaviors not specified. | SUM(expr) OVER (window) is supported; window may include PARTITION BY and ORDER BY clauses. | SELECT SUM(qty) OVER (PARTITION BY seller ORDER BY saletime) FROM sales; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| COUNT OVER | Default frame behavior is not described; no NULL-handling or other behavioral notes provided. | COUNT(expr) OVER (window) is supported; window clauses such as PARTITION BY and ORDER BY are shown. | SELECT COUNT() OVER (PARTITION BY seller ORDER BY saletime) FROM sales; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| MAX OVER | No behavioral details such as default frames or NULL-handling are described. | MAX(expr) OVER (window) is supported; examples show PARTITION BY and ORDER BY within the window clause. | SELECT MAX(price) OVER (PARTITION BY category ORDER BY saletime) FROM sales; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| MIN OVER | No behavioral details such as default frames, NULL-handling, or ordering requirements are described. | MIN(expr) OVER (window) is supported; examples show PARTITION BY and ORDER BY usage. | SELECT MIN(price) OVER (PARTITION BY category ORDER BY saletime) FROM sales; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| AVG OVER | No behavioral details such as default frames, NULL-handling, or ordering requirements are described. | AVG(expr) OVER (window) is supported; examples show PARTITION BY and ORDER BY usage. | SELECT AVG(price) OVER (PARTITION BY category ORDER BY saletime) FROM sales; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| LEAD | No behavioral details such as default frame behavior, NULL-handling specifics, or ordering rules are described. | Syntax supports LEAD(value_expr[, offset[, default]]) OVER (window); offset and default are optional. | SELECT LEAD(saletime, 1) OVER (PARTITION BY seller ORDER BY saletime) AS next_sale FROM sales; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| LAG | No behavioral details such as default frames, NULL-handling, or ordering rules are described. | Syntax supports LAG(value_expr[, offset[, default]]) OVER (window); offset and default are optional. | SELECT LAG(saletime, 1) OVER (PARTITION BY seller ORDER BY saletime) AS prev_sale FROM sales; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| NTILE | No behavioral details such as frame defaults, NULL-handling rules, or ordering requirements are described. | Syntax supports NTILE(num_buckets) OVER (window); num_buckets must be a positive integer. | SELECT NTILE(4) OVER (ORDER BY saletime) AS quartile FROM sales; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| PERCENT_RANK | No behavioral details such as default frames, NULL-handling, or ordering restrictions are described. | Syntax supports PERCENT_RANK() OVER (window); no arguments are permitted. | SELECT PERCENT_RANK() OVER (PARTITION BY category ORDER BY price) AS pct_rank FROM products; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| CUME_DIST | No behavioral details such as default frame clause, NULL-handling, tie behavior, or ordering requirements are described. | Syntax supports CUME_DIST() OVER (window); function takes no arguments. | SELECT CUME_DIST() OVER (ORDER BY price) AS cumedist FROM products; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| RANK | No behavioral details such as default frame clause, NULL-handling, or tie-handling specifics are described. | Syntax supports RANK() OVER (window); requires ORDER BY inside the window. | SELECT RANK() OVER (ORDER BY price) AS price_rank FROM products; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| DENSE_RANK | No behavioral details such as default frame behavior, NULL-handling, or tie-resolution specifics are described. | Syntax supports DENSE_RANK() OVER (window); ORDER BY is required inside the window clause. | SELECT DENSE_RANK() OVER (ORDER BY price) AS price_dense_rank FROM products; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| ROW_NUMBER | No behavioral details such as default frame definition, NULL-handling, or ordering semantics are described. | Syntax supports ROW_NUMBER() OVER (window); ORDER BY is required inside the window clause. | SELECT ROW_NUMBER() OVER (ORDER BY price) AS price_rownum FROM products; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| FIRST VALUE | No behavioral details such as default frame clause, NULL-handling, or ordering constraints are described. | Syntax supports FIRST_VALUE(value_expr) OVER (window); window must include ORDER BY. | SELECT FIRST_VALUE(price) OVER (ORDER BY price) AS first_val FROM products; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| LAST VALUE | No behavioral details such as default frame behavior, NULL-handling, or ordering constraints are described. | Syntax supports LAST_VALUE(value_expr) OVER (window); requires ORDER BY inside the window clause. | SELECT LAST_VALUE(price) OVER (ORDER BY price) AS last_val FROM products; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| NTH VALUE | No behavioral details such as default frame behavior, NULL-handling, ordering constraints, or boundary rules are described. | Syntax supports NTH_VALUE(value_expr, integer_expr) OVER (window); integer_expr specifies the row position. | SELECT NTH_VALUE(price, 3) OVER (ORDER BY price) AS third_val FROM products; | yes | Amazon Redshift auto-upgrades all clusters and does not publish SQL-by-version details, so minimum version is not applicable. | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | Uses standard window frame rules; requires ORDER BY for cumulative windows; aggregate operates per partition. | Follows standard window function syntax: SUM(expr) OVER ( [PARTITION BY ...] [ORDER BY ...] [frame] ) | SELECT dept, salary, SUM(salary) OVER(PARTITION BY dept ORDER BY salary) AS running_total FROM employees; | yes | 3.25.0 | Link | Learn More |
| COUNT OVER | Uses standard SQLite window processing rules; aggregate window functions follow normal frame semantics; default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW unless overridden | COUNT(expr) or COUNT() used as an aggregate window function with the OVER clause supporting PARTITION BY, ORDER BY, and optional frame clauses | SELECT dept, COUNT() OVER (PARTITION BY dept) AS dept_count FROM employees; | yes | 3.25.0 | Link | Learn More |
| MAX OVER | Follows SQLite's standard window-processing rules; default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present unless overridden | Uses standard aggregate window function syntax: MAX(expr) OVER ([PARTITION BY ...] [ORDER BY ...] [frame]) | SELECT dept, MAX(salary) OVER (PARTITION BY dept) AS max_salary FROM employees; | yes | 3.25.0 | Link | Learn More |
| MIN OVER | Follows SQLite's standard window-processing rules; default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present unless overridden | Uses standard aggregate window syntax: MIN(expr) OVER ([PARTITION BY ...] [ORDER BY ...] [frame]) | SELECT dept, MIN(salary) OVER (PARTITION BY dept) AS min_salary FROM employees; | yes | 3.25.0 | Link | Learn More |
| AVG OVER | Follows SQLite.s standard window-processing rules; aggregate windows use default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present unless overridden | Uses standard aggregate window syntax: AVG(expr) OVER ([PARTITION BY ..] [ORDER BY ...] [frame]) | SELECT dept, AVG(salary) OVER (PARTITION BY dept) AS avg_salary FROM employees; | yes | 3.25.0 | Link | Learn More |
| LEAD | Returns the value from a subsequent row based on ordering; offset defaults to 1; default value returned when no subsequent row exists | Syntax: LEAD(expr[, offset[, default]]) OVER ( [PARTITION BY ...] [ORDER BY ...] ) | SELECT val, LEAD(val) OVER (ORDER BY id) AS next_val FROM t; | yes | 3.25.0 | Link | Learn More |
| LAG | Returns a value from a preceding row; offset defaults to 1; returns default (or NULL) when no preceding row exists | Syntax: LAG(expr[, offset[, default]]) OVER ([PARTITION BY ...] [ORDER BY ...]) | SELECT val, LAG(val) OVER (ORDER BY id) AS prev_val FROM t; | yes | 3.25.0 | Link | Learn More |
| NTILE | Distributes rows into n buckets of approximately equal size based on ORDER BY; requires ORDER BY; bucket numbers start at 1 | Syntax: NTILE(n) OVER ([PARTITION BY ...] [ORDER BY ...]) | SELECT val, NTILE(4) OVER (ORDER BY val) AS bucket FROM t; | yes | 3.25.0 | Link | Learn More |
| PERCENT_RANK | Computes (rank-1) / (total_rows-1) within the window; requires ORDER BY; returns 0 for the first row | Syntax: PERCENT_RANK() OVER ([PARTITION BY ...] [ORDER BY ...]) | SELECT val, PERCENT_RANK() OVER (ORDER BY val) AS pct_rank FROM t; | yes | 3.25.0 | Link | Learn More |
| CUME_DIST | Returns the cumulative distribution: number_of_rows_with_value <= current_value divided by total_rows; requires ORDER BY; result is in (0,1) | Syntax: CUME_DIST() OVER ([PARTITION BY ...] [ORDER BY ...]) | SELECT val, CUME_DIST() OVER (ORDER BY val) AS cume FROM t; | yes | 3.25.0 | Link | Learn More |
| RANK | Assigns rank with gaps: equal values receive the same rank; the next row after a tie jumps ahead by the size of the tie group; requires ORDER BY | Syntax: RANK() OVER ([PARTITION BY ...] [ORDER BY ...]) | SELECT val, RANK() OVER (ORDER BY val) AS rnk FROM t; | yes | 3.25.0 | Link | Learn More |
| DENSE_RANK | Assigns consecutive ranks with no gaps; equal ORDER BY values receive the same rank | Syntax: DENSE_RANK() OVER ([PARTITION BY ...] [ORDER BY ...]) | SELECT val, DENSE_RANK() OVER (ORDER BY val) AS dr FROM t; | yes | 3.25.0 | Link | Learn More |
| ROW_NUMBER | Assigns a unique sequential number to each row in the window; numbering always increases by 1; requires ORDER BY to define row order | Syntax: ROW_NUMBER() OVER ([PARTITION BY ...] [ORDER BY ...]) | SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS rn FROM t; | yes | 3.25.0 | Link | Learn More |
| FIRST VALUE | Returns the value of expr from the first row in the window frame as defined by ORDER BY and the frame clause; influenced by default or explicit frame definitions | Syntax: FIRST_VALUE(expr) OVER ([PARTITION BY ...] [ORDER BY ...] [frame]) | SELECT val, FIRST_VALUE(val) OVER (ORDER BY val) AS first_val FROM t; | yes | 3.25.0 | Link | Learn More |
| LAST VALUE | Returns the value of expr from the last row in the window frame; affected by ORDER BY and frame - default frame may cause LAST_VALUE to return the current row unless an appropriate frame is specified | Syntax: LAST_VALUE(expr) OVER ([PARTITION BY ...] [ORDER BY ...] [frame]) | SELECT val, LAST_VALUE(val) OVER (ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val FROM t; | yes | 3.25.0 | Link | Learn More |
| NTH VALUE | Returns the value of expr from the nth row of the window frame; depends on ORDER BY and frame; returns NULL if n is out of range | Syntax: NTH_VALUE(expr, n) OVER ([PARTITION BY ...] [ORDER BY ...] [frame]) | SELECT val, NTH_VALUE(val, 3) OVER (ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS nth_val FROM t; | yes | 3.25.0 | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | When no frame clause is specified, SUM() OVER(...) by default aggregates over the entire partition; window-aggregation-group defaults apply. Also: an OLAP specification cannot be used in WHERE, GROUP BY, HAVING, VALUES, SET, or as an argument to another aggregate. (As per Db2 for z/OS OLAP spec page) | can use aggregate functions (like SUM) with an OVER() clause - i.e. as an OLAP aggregation specification | SELECT SUM(sales) OVER (PARTITION BY region) FROM sales_table; | yes | No preserved IBM doc we found confirms exact version; earliest accessible docs date to DB2 8, community sources suggest earlier versions lacked support. | Link | Learn More |
| COUNT OVER | COUNT() OVER(...) returns the count of non-NULL values (or rows with COUNT() ) in the partition or window frame; if DISTINCT is used, OVER cannot include ORDER BY or frame clause. NULL values are ignored unless all values are NULL, in which case result is NULL. Aggregation specification cannot be used in WHERE, GROUP BY, HAVING, VALUES, SET, or as an argument to another aggregate. | COUNT() can be used with an OVER() clause as part of the OLAP aggregation-specification | SELECT COUNT(*) OVER (PARTITION BY department_id) FROM employee; | yes | No preserved IBM doc we found confirms exact version; earliest accessible docs date to DB2 8, community sources suggest earlier versions lacked support. | Link | Learn More |
| MAX OVER | MAX() OVER(...) returns the maximum value in the partition or in the defined window frame. NULL values are ignored (unless all values are NULL, in which case result is NULL). Aggregation-specification cannot be used in WHERE, VALUES, GROUP BY, HAVING, SET, or as argument to another aggregate. | MAX() can be used with an OVER(...) clause as part of the OLAP aggregation-specification | SELECT MAX(score) OVER (PARTITION BY class) FROM exam_results; | yes | No preserved IBM doc we found confirms exact version; earliest accessible docs date to DB2 8, community sources suggest earlier versions lacked support. | Link | Learn More |
| MIN OVER | MIN() OVER(...) returns the minimum value in the partition or defined window frame. NULL values are ignored (unless all values are NULL, in which case result is NULL). Aggregation-specification cannot be used in WHERE, VALUES, GROUP BY, HAVING, SET, or as argument to another aggregate. | MIN() can be used with an OVER(...) clause as part of the OLAP aggregation-specification | SELECT MIN(balance) OVER (PARTITION BY customer_id) FROM accounts; | yes | No preserved IBM doc we found confirms exact version; earliest accessible docs date to DB2 8, community sources suggest earlier versions lacked support. | Link | Learn More |
| AVG OVER | AVG() OVER(...) returns the average of the applicable values in the partition or defined window; nulls are ignored (unless all values in the set are null, in which case result is null). Aggregation-specification (window-aggregate) cannot be used in WHERE, VALUES, GROUP BY, HAVING, SET, or as argument to another aggregate. | AVG() can be used with an OVER(...) clause as part of the OLAP aggregation-specification | SELECT MIN(balance) OVER (PARTITION BY customer_id) FROM accounts; | yes | No preserved IBM doc we found confirms exact version; earliest accessible docs date to DB2 8, community sources suggest earlier versions lacked support. | Link | Learn More |
| LEAD | Returns the value from the row offset positions after the current row within the partition; NULL returned when out of range unless default-value is supplied; IGNORE NULLS skips rows where expression is NULL. Warning: LEAD is passthrough-only and cannot run on Db2 for z/OS without acceleration. |
Supports LEAD(expr, offset, default) with optional IGNORE NULLS; offset must be a positive integer and defaults to 1; default-value returned when offset exceeds partition scope. | SELECT LEAD(salary) OVER (PARTITION BY dept ORDER BY empno) FROM employee; | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| LAG | Returns the value from the row offset positions before the current row within the partition; returns NULL or default-value when offset goes out of range; IGNORE NULLS skips rows where the expression is NULL. Warning: LAG is passthrough-only and cannot run on Db2 for z/OS without acceleration. |
Supports LAG(expr, offset, default); offset must be a positive integer and defaults to 1; optional default-value and IGNORE NULLS clause. | SELECT LAG(salary) OVER (PARTITION BY dept ORDER BY empno) FROM employee; | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| NTILE | Distributes rows into n groups of equal size or as equal as possible; tile numbers start at 1 and increase with ordering; must be used with an ORDER BY clause in the OLAP specification. | NTILE(n) assigns each row to one of n tiles; requires ORDER BY within the window specification. | SELECT NTILE(4) OVER (PARTITION BY department ORDER BY salary) FROM employee; | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| PERCENT_RANK | Returns the relative rank of the current row within its partition as a decimal between 0 and 1; first row receives 0; if the partition has only one row, the result is 0. | Requires ORDER BY in the OLAP specification; computes (rank-1)/(rows_in_partition-1). | SELECT PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary) AS pct_rank FROM employees | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| CUME_DIST | Returns the cumulative distribution of the current row within its partition as a decimal between 0 and 1; first row receives a value > 0 depending on duplicates; single-row partitions return 1. | Requires ORDER BY in the OLAP specification; computes number_of_rows_with_value_<=_current / rows_in_partition. | SELECT CUME_DIST() OVER (PARTITION BY dept ORDER BY salary) AS cume_dist FROM employees | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| RANK | Returns the rank of the current row within its partition based on the ORDER BY clause; tied rows receive the same rank and the next rank value is incremented by the number of tied rows. | Requires ORDER BY in the OLAP specification; assigns the same rank to ties and leaves gaps after ties. | SELECT RANK() OVER (PARTITION BY dept ORDER BY salary) AS rnk FROM employees | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| DENSE_RANK | Returns the rank of the current row within its partition; tied rows receive the same rank, and the next distinct value receives the immediately following rank (no gaps). | Requires ORDER BY in the OLAP specification; assigns consecutive rank values with no gaps for ties. | SELECT DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary) AS dense_rnk FROM employees | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| ROW_NUMBER | Numbers rows sequentially beginning with 1 based on the ORDER BY clause; no ties because each row always receives a distinct row number. | Requires ORDER BY in the OLAP specification; assigns a unique sequential integer to each row within the partition. | SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary) AS row_num FROM employees | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| FIRST VALUE | Returns the value of expr from the first row of the window frame after ORDER BY is applied; frame defaults may cause FIRST_VALUE to reflect the first row of the entire partition unless a frame clause is specified. | Requires ORDER BY in the OLAP specification; returns FIRST_VALUE(expr); default window frame determines which row is considered 'first'. | SELECT FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary) AS first_salary FROM employees | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| LAST VALUE | Returns the value of expr from the last row of the window frame after ORDER BY is applied; default frame behavior can cause unintuitive results unless a frame is explicitly defined. | Requires ORDER BY in the OLAP specification; returns LAST_VALUE(expr); default window frame may cause LAST_VALUE to return the last row of the entire partition unless a frame clause is specified. | SELECT LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary) AS last_salary FROM employees | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| NTH VALUE | Returns the value of expr from the nth row of the window frame; if n exceeds the number of rows in the frame, result is NULL; behavior depends on the window frame unless explicitly defined. | Requires ORDER BY in the OLAP specification; syntax uses NTH_VALUE(expr, n); n must be a positive integer; default frame may affect which row is considered the nth row. | SELECT NTH_VALUE(salary, 3) OVER (PARTITION BY dept ORDER BY salary) AS third_salary FROM employees | yes | version 9 (z/OS) / 9.7 (LUW). | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | Supports window frames; example shows ORDER BY and RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | SUM(expr) OVER ( [PARTITION BY ...] [ORDER BY ...] [window_frame] ) | SELECT department, salary, SUM(salary) OVER (PARTITION BY department) AS sum_over_department FROM employees; | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| COUNT OVER | Supports window frames; example shows ORDER BY and RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | COUNT([ expression ] | *) OVER ( [ PARTITION BY ... ] [ ORDER BY ... ] [ window_frame ] ) | SELECT department, salary, COUNT(*) OVER (PARTITION BY department) AS count_over_department FROM employees; | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| MAX OVER | Supports window frames; example shows ORDER BY and RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | MAX(expr) OVER ( [PARTITION BY ...] [ORDER BY ...] [window_frame] ) | SELECT department, salary, MAX(salary) OVER (PARTITION BY department) AS max_over_department FROM employees; | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| MIN OVER | Supports window frames; example uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | MIN(expr) OVER ( [PARTITION BY ...] [ORDER BY ...] [window_frame] ) | SELECT department, salary, MIN(salary) OVER (PARTITION BY department) AS min_over_department FROM employees; | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| AVG OVER | Supports window frames; example uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | AVG(expr) OVER ( [PARTITION BY ...] [ORDER BY ...] [window_frame] ) | SELECT department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_over_department FROM employees; | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| LEAD | Offset defaults to 1 when not specified; NULL is returned when offset goes beyond partition unless DEFAULT is provided | LEAD(expr [, offset [, default ]]) OVER ( [PARTITION BY ...] ORDER BY ... ) | SELECT LEAD(salary, 1, 0) OVER (PARTITION BY department ORDER BY hire_date) AS next_salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| LAG | Offset defaults to 1; returns NULL when offset exceeds partition unless DEFAULT is provided | LAG(expr [, offset [, default ]]) OVER ( [PARTITION BY ...] ORDER BY ... ) | SELECT LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY hire_date) AS prev_salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| NTILE | n must be a positive integer; assigns each row to one of n buckets of nearly equal size | NTILE(n) OVER ( [PARTITION BY ...] ORDER BY ... ) | SELECT NTILE(4) OVER (ORDER BY salary) AS salary_quartile, employee_id, salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| PERCENT_RANK | Returns (rank - 1) / (total_rows_in_partition - 1); first row always returns 0; last row always returns 1; requires ORDER BY | PERCENT_RANK() OVER ( [PARTITION BY ...] ORDER BY ... ) | SELECT PERCENT_RANK() OVER (ORDER BY salary) AS salary_percent_rank, employee_id, salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| CUME_DIST | Returns the cumulative distribution: number of rows with value <= current value divided by total rows in the partition; requires ORDER BY | CUME_DIST() OVER ( [PARTITION BY ...] ORDER BY ... ) | SELECT CUME_DIST() OVER (ORDER BY salary) AS salary_cume_dist, employee_id, salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| RANK | Rows with equal values receive the same rank; ranks skip after ties | RANK() OVER ( [PARTITION BY ...] ORDER BY ... ) | SELECT RANK() OVER (ORDER BY salary) AS salary_rank, employee_id, salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| DENSE_RANK | Rows with equal values receive the same rank; ranks do NOT skip after ties | DENSE_RANK() OVER ( [PARTITION BY ...] ORDER BY ... ) | SELECT DENSE_RANK() OVER (ORDER BY salary) AS salary_dense_rank, employee_id, salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| ROW_NUMBER | Assigns a unique sequential number starting at 1 within each partition, ordered by ORDER BY | ROW_NUMBER() OVER ( [PARTITION BY ...] ORDER BY ... ) | SELECT ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn, employee_id, department, salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| FIRST VALUE | Returns the first value in the window; result depends on ORDER BY and the window frame; default frame may include preceding rows depending on specification | FIRST_VALUE(expr) OVER ( [PARTITION BY ...] ORDER BY ... [window_frame] ) | SELECT employee_id, salary, FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) AS first_salary FROM employees; | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| LAST VALUE | Returns the last value in the window frame; result depends on ORDER BY and window frame boundaries | LAST_VALUE(expr) OVER ( [PARTITION BY ...] ORDER BY ... [window_frame] ) | SELECT LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary, employee_id, salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| NTH VALUE | Returns the value of the n-th row in the window frame; requires ORDER BY; frame boundaries determine which row is considered n-th; n must be a positive integer | NTH_VALUE(expr, n) OVER ( [PARTITION BY ...] ORDER BY ... [window_frame] ) | SELECT NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_salary, employee_id, salary FROM employees | yes | Databricks SQL Warehouses use a continuously updated engine without user-visible versions, so no minimum version can be specified. | Link | Learn More |
| Function | Behavior Notes | Syntax Notes | Example | Supported | Min Version | Documentation | Learn |
|---|---|---|---|---|---|---|---|
| SUM OVER | Window form returns one value per row in the window instead of collapsing rows like GROUP BY. | Use standard sum(...) OVER (window_spec) as a window aggregate. | SELECT category, amount, SUM(amount) OVER (PARTITION BY category) AS category_sum FROM sales; | yes | 1.4 | Link | Learn More |
| COUNT OVER | COUNT used with OVER returns one value per row in the window rather than collapsing rows as in GROUP BY. | Standard aggregate count(*) OVER (window_spec) is allowed; Spark explicitly states aggregate functions may be used with OVER. | SELECT category, amount, COUNT(*) OVER (PARTITION BY category) AS category_count FROM sales; | yes | 1.4 | Link | Learn More |
| MAX OVER | When used with OVER`, MAX returns one value per row within the window rather than collapsing rows like GROUP BY. | `max(expr) OVER (window_spec)is allowed because Spark explicitly states aggregate functions may be used withOVER. | SELECT category, amount, MAX(amount) OVER (PARTITION BY category) AS category_max FROM sales; | yes | 1.4 | Link | Learn More |
| MIN OVER | When used with OVER`, MIN returns one value per row within the specified window rather than collapsing rows as in GROUP BY. | `min(expr) OVER (window_spec)is allowed because Spark explicitly states aggregate functions may be used withOVER. | SELECT category, amount, MIN(amount) OVER (PARTITION BY category) AS category_min FROM sales; | yes | 1.4 | Link | Learn More |
| AVG OVER | AVG used with OVER` returns one value per row in the window rather than collapsing rows like GROUP BY. | `avg(expr) OVER (window_spec)is allowed; Spark explicitly states aggregate functions may be used withOVER. | SELECT category, amount, AVG(amount) OVER (PARTITION BY category) AS category_avg FROM sales; | yes | 1.4 | Link | Learn More |
| LEAD | LEAD returns the value from a subsequent row based on the ordering defined in the window; if no row exists, returns NULL or the provided default. | `LEAD(expr, offset, default)is listed with its full signature; Spark shows it must be used with anOVER` clause. | SELECT id, value, LEAD(value, 1) OVER (ORDER BY id) AS next_value FROM table; | yes | 1.4 | Link | Learn More |
| LAG | Returns the value from a preceding row based on window ordering; if that row does not exist, returns NULL unless a default is provided. | `LAG(expr, offset, default)is documented with optional parameters and requires anOVER` clause. | SELECT id, value, LAG(value, 1) OVER (ORDER BY id) AS prev_value FROM table; | yes | 1.4 | Link | Learn More |
| NTILE | NTILE assigns each row to one of n buckets defined by the window ordering; buckets are filled as evenly as possible. | Syntax is NTILE(n) and it must be used with an OVER clause; Spark documents an integer argument representing bucket count. | SELECT id, NTILE(4) OVER (ORDER BY id) AS tile FROM table; | yes | 1.4 | Link | Learn More |
| PERCENT_RANK | Returns the relative rank of the current row: (rank - 1) / (rows_in_partition - 1); defined only over the window's ORDER BY. | `PERCENT_RANK()` takes no arguments and must be used with an OVER clause; Spark documents it as a window function. | SELECT id, PERCENT_RANK() OVER (ORDER BY id) AS pct_rank FROM table; | yes | 1.4 | Link | Learn More |
| CUME_DIST | Returns the cumulative distribution within the window's ORDER BY. | `CUME_DIST()` takes no arguments and must be used with an OVER clause. | SELECT category, amount, CUME_DIST() OVER (PARTITION BY category ORDER BY amount) AS cume_dist FROM sales; | yes | 1.4 | Link | Learn More |
| RANK | Returns the rank of the current row with gaps after ties, determined by the ORDER BY in the window. | `RANK()` takes no arguments and must be used with an OVER clause; documented as a window ranking function. | SELECT category, amount, RANK() OVER (PARTITION BY category ORDER BY amount) AS category_rank FROM sales; | yes | 1.4 | Link | Learn More |
| DENSE_RANK | Assigns consecutive ranks without gaps; tied rows receive the same rank and the next rank is incremented by 1. | `DENSE_RANK()` takes no arguments and must be used with an OVER clause; documented as a window ranking function. | SELECT category, amount, DENSE_RANK() OVER (PARTITION BY category ORDER BY amount) AS category_dense_rank FROM sales; | yes | 1.4 | Link | Learn More |
| ROW_NUMBER | Assigns a unique sequential number to each row in the window, ordered by the window's ORDER BY clause. | `ROW_NUMBER()` takes no arguments and must be used with an OVER clause. | SELECT category, amount, ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount) AS row_num FROM sales; | yes | 1.4 | Link | Learn More |
| FIRST VALUE | Returns the first value in the window frame based on the window's ORDER BY and frame specification. | `FIRST_VALUE(expr)`requires an OVER clause; Spark documents it with one required argument. | SELECT category, amount, FIRST_VALUE(amount) OVER (PARTITION BY category ORDER BY amount) AS first_amount FROM sales; | yes | 1.4 | Link | Learn More |
| LAST VALUE | Returns the last value in the window frame based on the window's ORDER BY and frame specification. | `LAST_VALUE(expr)` requires an OVER clause; Spark documents one required argument. | SELECT category, amount, LAST_VALUE(amount) OVER (PARTITION BY category ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount FROM sales; | yes | 1.4 | Link | Learn More |
| NTH VALUE | Returns the n-th value in the window frame based on the window's ORDER BY and frame specification. | `NTH_VALUE(expr, n)requires anOVER` clause; Spark documents two required arguments. | SELECT category, amount, NTH_VALUE(amount, 2) OVER (PARTITION BY category ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_amount FROM sales; | yes | 1.4 | Link | Learn More |