Can my DB?

Window Functions

Compute values over a set of rows defined relative to each row, without collapsing those rows the way GROUP BY does.

window functions analytic functions OVER clause windowed aggregates
window analytics ranking aggregation OVER PARTITION BY
DatabaseStatusSyntax FamilyMin Version
PostgreSQL full sql-standard 8.4
MySQL full sql-standard 8.0
MariaDB full sql-standard 10.2
Oracle full sql-standard 8i
SQL Server full sql-standard 2005
SQLite full sql-standard 3.25.0
Redshift full sql-standard -
DB2 full sql-standard -
Snowflake full sql-standard -
BigQuery full sql-standard -

Compare Databases

Database Details

PostgreSQL full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • All three frame types (ROWS, RANGE, GROUPS) are available; GROUPS was added in 11.
  • Supports FILTER on aggregate window functions and the named WINDOW clause.
Docs: Window Functions, Window Function Calls
MySQL full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS `rank`,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • Added in 8.0; 5.7 and earlier require session-variable tricks to emulate ranking.
  • Named WINDOW clause is supported. GROUPS frame type is not.
Docs: Window Functions
MariaDB full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS `rank`,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • Added in 10.2. PERCENT_RANK and CUME_DIST arrived in 10.3.2.
Docs: Window Functions
Oracle full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • Oracle calls them analytic functions and has shipped them since 8i.
  • Proprietary extras: RATIO_TO_REPORT, LISTAGG as a window function, KEEP (DENSE_RANK FIRST/LAST).
Docs: Analytic Functions
SQL Server full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • Ranking functions shipped in 2005; navigation and distribution functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENT_RANK, CUME_DIST) in 2012.
  • ROWS and RANGE frame types only; no GROUPS.
Docs: Window Functions (Transact-SQL)
SQLite full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • Added in 3.25.0 (2018-09-15). GROUPS frame type added in 3.28.0.
  • Named WINDOW clause is supported.
Docs: Window Functions
Redshift full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • ROWS frame is fully supported. RANGE only accepts UNBOUNDED PRECEDING and CURRENT ROW; value-based RANGE offsets are not. GROUPS is not supported.
  • Extras: PERCENTILE_CONT, PERCENTILE_DISC, LISTAGG, MEDIAN, and APPROXIMATE PERCENTILE_DISC.
  • Named WINDOW clause is supported.
Docs: Window Function Synopsis
DB2 full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • DB2 calls them OLAP specifications. No NTH_VALUE.
Docs: OLAP Specifications
Snowflake full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • QUALIFY lets you filter on a window result inline without wrapping in a subquery.
Docs: Window Functions
BigQuery full 
SELECT
  employee_id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;
  • QUALIFY lets you filter on a window result inline without wrapping in a subquery.
Docs: Analytic Function Concepts